Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    59
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Hide all labels and text boxes in a user form

    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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,478
    Thanks
    211
    Thanked 849 Times in 781 Posts
    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.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    59
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks RG but how do I limit it to labels and text boxes, not all controls?

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,478
    Thanks
    211
    Thanked 849 Times in 781 Posts
    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.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. The Following 2 Users Say Thank You to RetiredGeek For This Useful Post:

    orangehat (2013-02-22),Raymond (2013-01-04)

  6. #5
    Star Lounger
    Join Date
    Jun 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    59
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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 SouthAfrica.gif

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,478
    Thanks
    211
    Thanked 849 Times in 781 Posts
    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.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,939
    Thanks
    0
    Thanked 94 Times in 90 Posts
    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.
    Regards,
    Rory
    Microsoft MVP - Excel.

  9. #8
    New Lounger
    Join Date
    Feb 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •