Show/Hide Controls on a User-Form using a Checkbox

Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010

Home





A question asked by one of website visitors Marshall:

Dear Dinesh,
Thank you for all your help, my userform has developed since we last spoke, I have updated it using your example and this is working well.
I understanding have attached this for you to see.

My burning question now is, can a combobox be hidden and only appear after an input from another text or combo box.

Example, on my form the first input box is a textbox demanding that you choose an option even if the option is zero.
What I would like is for all the comboboxes below be hidden until I have made the selection in  the first textbox.

Then the user continues to fill in the boxes, can this be done?

Regards
Marshall

Another Microsoft Excel VBA question?
I'm creating userforms in microsoft excel, and I want the forms to come up with the textboxes hidden until a check box is selected, what code do I use for this?
Watch the video (about 14 MB) below to see how the controls are inserted, coded and executed to provide an elegant solution.

Here's the code for the checkbox:
Private Sub CheckBox1_Click()
For Each objCtrl In Me.Controls
'When you create the first TextBox the default Name is 'TextBox1'; the second TextBox's name is TextBox2 & so on
'Therefore we check for 'Text' below using the 'Left' text function
'If you change the Name of your controls then you must code accordingly
If Left(objCtrl.Name, 4) = "Text" Then objCtrl.Visible = CheckBox1.Value
Next
End Sub

This is the VBA code when you start or run the user-form:
Private Sub UserForm_Initialize()
CheckBox1.Value = False
For Each objCtrl In Me.Controls
If Left(objCtrl.Name, 4) = "Text" Then objCtrl.Visible = False
Next
End Sub

You can similarly use any control like a 'text box' or a 'combo-box' to show or hide other controls on your user-form by carefully coding the relevant control.

Notice how the 'for each loop' is used with good effect. For more 'for loop' solutions you can search the macros area of the website.
If you wish to know more about how to create user-forms, insert controls and add codng to the controls, you can watch the videos here under VBA user forms..
                                                        





Home
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010