Hide all labels and text boxes in a user form

Topic

New Reply

I have a user form in excel that has a number of labels and text boxes. When the form opens, I want all of these to be hidden and after the user has entered info, then they must be visible.
Is there any way to do this other than by referencing each label/text box individually with .visible = false / visible = true.
I am adding more labels/boxes to the form so would like to be able to reference them all at once.

Thanks

Raymond

Reply | Quote

Replies

Raymond,

If you truely want all of them to be hidden you could use a For Each loop with the Controls collection placed in the Form_Open or Form_Current event depending on your usage.
Ex:

Code:

   For Each ctl in Me.Controls
      ctl.Visible = False
   Next ctl

Note: this is Air code {untested } but the theory should work.
You could also include some logic in the loop to exclude certian controls if necessary.:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks RG but how do I limit it to labels and text boxes, not all controls?:confused:

Reply | Quote

Raymond,

Ok, here’s some tested code.

Code:

Option Explicit

Private Sub UserForm_Initialize()

  Dim ctl As Control
  
  For Each ctl In Me.Controls
  
     With ctl
         If Left(.Name, 3) = "lbl" Then .Visible = False
         If Left(.Name, 6) = "txtbox" Then .Visible = False
     End With
  
  Next ctl

End Sub

Note: the code assumes that you use a naming convention for your controls. I normally use the prefix of “lbl” for lables and “txtbox” for text boxes. You can use what ever convention you wish as long as you are consistent and change the code accordingly. You could also use the Tag property if you wish and place a value line HIDE in each control’s tag property and then have the code check for that which would make the code a little more straight forward. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks RG – I had also queried on another forum /http://it.toolbox.com/home/ and received the following code from John Madden which does eactly what I want:

Code:

Dim ctrl As Control 
For Each ctrl In Me.Controls 

 If TypeOf ctrl Is msforms.TextBox Or TypeOf ctrl Is msforms.Label Then 
 ctrl.Visible = False 
 End If 

Next 

Thanks once again for your assistance.

Raymond 32749-SouthAfrica

Reply | Quote

Raymond,

FYI you should always mention when your cross post and provide links to the other posts. Thanks for posting the solution from the other post as it’s a new trick for me and I’m sure others here. Glad you have what you need. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

FYI, you can also use TypeName which is often a better choice:

Code:

   Dim ctrl                   As Control   
   For Each ctrl In Me.Controls
   
      Select Case LCase$(TypeName(ctrl))
         Case "textbox", "label"
            ctrl.Visible = False
      End Select


   Next ctrl

For example, TypeOf ctrl Is MSForms.Checkbox will return True for checkboxes, optionbuttons and togglebuttons, since the latter two inherit from the CheckBox control.

Reply | Quote

Another option is to place all your controls in a separate frame and then hide the frame via fraName.visible = false
as you would the controls individually.

Reply | Quote