Excel VBA UserForm - Easier way to Hide/Show TextBoxes/Labels based on ComboBox Selection?

[Alan_P]

Hi All,

Bascially I have created a UserForm that has a ComboBox and depending on the number selected I want it to show that number of Labels/TextBoxes...

so if I select "0" nothing is shown, if I select "1" one set of Labels/TextBoxes is shown, select "2" and two sets of Labels/TextBoxes are shown... but also if I have selected "2" and then select "1" I want the second set to be hidden again...

I have the code below, which works fine, but it is very long winded and would get extremely long if I want to go past four sets...

Does anyone know if there is an easier way to do this?

Also I know I should have renamed the Label/TexBoxes to make it easer but I was adding things and making it up as I went along...

I'm using Excel 2010 on windows 7.

Any help would be much appreciated!
Thanks,
AlanP.

P.s. This is my first time posting

Sub UnHide_NewRoutings()
    If (Engineering.ComboBox2.value) = "0" Then
        Engineering.Label4.Visible = False
        Engineering.TextBox5.Visible = False
        Engineering.Label9.Visible = False
        Engineering.TextBox9.Visible = False
        
        Engineering.Label6.Visible = False
        Engineering.TextBox6.Visible = False
        Engineering.Label10.Visible = False
        Engineering.TextBox10.Visible = False
        
        Engineering.Label7.Visible = False
        Engineering.TextBox7.Visible = False
        Engineering.Label11.Visible = False
        Engineering.TextBox11.Visible = False
        
        Engineering.Label8.Visible = False
        Engineering.TextBox8.Visible = False
        Engineering.Label12.Visible = False
        Engineering.TextBox12.Visible = False
        
    End If
    
    If (Engineering.ComboBox2.value) = "1" Then
        Engineering.Label4.Visible = True
        Engineering.TextBox5.Visible = True
        Engineering.Label9.Visible = True
        Engineering.TextBox9.Visible = True
        
        Engineering.Label6.Visible = False
        Engineering.TextBox6.Visible = False
        Engineering.Label10.Visible = False
        Engineering.TextBox10.Visible = False
        
        Engineering.Label7.Visible = False
        Engineering.TextBox7.Visible = False
        Engineering.Label11.Visible = False
        Engineering.TextBox11.Visible = False
        
        Engineering.Label8.Visible = False
        Engineering.TextBox8.Visible = False
        Engineering.Label12.Visible = False
        Engineering.TextBox12.Visible = False
        
    End If
    
    If (Engineering.ComboBox2.value) = "2" Then
        Engineering.Label4.Visible = True
        Engineering.TextBox5.Visible = True
        Engineering.Label9.Visible = True
        Engineering.TextBox9.Visible = True
        
        Engineering.Label6.Visible = True
        Engineering.TextBox6.Visible = True
        Engineering.Label10.Visible = True
        Engineering.TextBox10.Visible = True
        
        Engineering.Label7.Visible = False
        Engineering.TextBox7.Visible = False
        Engineering.Label11.Visible = False
        Engineering.TextBox11.Visible = False
        
        Engineering.Label8.Visible = False
        Engineering.TextBox8.Visible = False
        Engineering.Label12.Visible = False
        Engineering.TextBox12.Visible = False
        
    End If
    
        If (Engineering.ComboBox2.value) = "3" Then
        Engineering.Label4.Visible = True
        Engineering.TextBox5.Visible = True
        Engineering.Label9.Visible = True
        Engineering.TextBox9.Visible = True
        
        Engineering.Label6.Visible = True
        Engineering.TextBox6.Visible = True
        Engineering.Label10.Visible = True
        Engineering.TextBox10.Visible = True
        
        Engineering.Label7.Visible = True
        Engineering.TextBox7.Visible = True
        Engineering.Label11.Visible = True
        Engineering.TextBox11.Visible = True
        
        Engineering.Label8.Visible = False
        Engineering.TextBox8.Visible = False
        Engineering.Label12.Visible = False
        Engineering.TextBox12.Visible = False
        
    End If
    
    If (Engineering.ComboBox2.value) = "4" Then
        Engineering.Label4.Visible = True
        Engineering.TextBox5.Visible = True
        Engineering.Label9.Visible = True
        Engineering.TextBox9.Visible = True
        
        Engineering.Label6.Visible = True
        Engineering.TextBox6.Visible = True
        Engineering.Label10.Visible = True
        Engineering.TextBox10.Visible = True
        
        Engineering.Label7.Visible = True
        Engineering.TextBox7.Visible = True
        Engineering.Label11.Visible = True
        Engineering.TextBox11.Visible = True
        
        Engineering.Label8.Visible = True
        Engineering.TextBox8.Visible = True
        Engineering.Label12.Visible = True
        Engineering.TextBox12.Visible = True
        
    End If
End Sub

 

[Darren Bartrup]

Hi and welcome to the board..

Not exactly an answer, but you can reference and change the visibility of controls on a form like this:

Private Sub CommandButton1_Click()

    Dim x As Long
    
    For x = 1 To 2
        Me.Controls("TextBox" & x).Visible = Not Me.Controls("TextBox" & x).Visible
        Me.Controls("Label" & x).Visible = Not Me.Controls("Label" & x).Visible
    Next x


End Sub

The .Visible = Not .Visible bit will change the visibility from TRUE to FALSE and back again.

 

[Alan_P]

Thanks Darren!

I will have a play with that and see if I can get it to work...

Cheers,
Alan.

 

[HarryS]

 ' look along
' 1 rename your controls  to  anynamexx1 othernamexx12  etc  or put numbers in the tag of each
' combobox2.change
'  MakeVisibles
'to aviod confusion of xx XX  have       Option Explicit :option compare text
Sub MakeVisibles()
    Dim FoCtrl As MSForms.Control, VId%, BoxVal%, Px%
    BoxVal = Engineering.ComboBox2.Value
    For Each FoCtrl In Engineering.Controls
        '
        ' if using tag  idea then use in this loop  these 4 lines instead of 5 below
        ' Vid=val(foctrl.tag)
        ' if Vid >0 then
        ' If BoxVal >= vid Then FoCtrl.Visible = True Else FoCtrl.Visible = False
        ' End If
        '
        Px = InStr(FoCtrl.Name, "xx")  ' assume not to many xx in names
        If Px > 0 Then
            VId = Val(Mid(FoCtrl.Name), Px + 2)  ' get rest of name
            If BoxVal >= Px Then FoCtrl.Visible = True Else FoCtrl.Visible = False
        End If
    Next FoCtrl
End Sub
'
' tag may be a simple solution to your current situation
' but it looks like it grew more and more complex and uncoordinated
' get used to using meaningfull coded names  like CatsNameXX23YY45
' this saves a lot of problems.
'
' if you can do class modules then the instance of any control can store lots of extra info
' by storing each in a collection
'eg
' a class module like
'Public WithEvents PickTabF As MSForms.Label
'Public LastLeftCtrl&, LastTopCtrl&
'Public pID%, pActionNa$  ' where in collection
'Private Sub PickTabF_Click() ' send back information
    '
    ' something like this can  act on   one  or more Forms
    'UFAF.CSAF.LabelHit pID, PickTabF.Name, pActionNa
    
    'Enginering.LabelHit pID, PickTabF.Name, pActionNa
    ' for all cases  send back to correct form =findUF(PickTabF) that sent event
   ' findUF(PickTabF).CSAF.LabelHit pID, PickTabF.Name, pActionNa
    
'End Sub

 

[harris818]

Hi, im doing something very similair to AlanP., i have a combo box where if the user selects Yes a label will appear and if the user selects no the label will remain hidden.

Private Sub UserForm_Initialize()
UserForm1.Label2.Visible = False
UserForm1.CommandButton2.Visible = False
End Sub


Private Sub CommandButton1_Click()
If (UserForm1.ComboBox1.Value) = "Yes" Then
UserForm1.Label2.Visible = True
UserForm1.CommandButton2.Visible = True


End If
End Sub




The issue im having is that when Yes is selected in the combo box the label does not appear accordingly.

Any help would be great
Thanks

 

[HarryS]

In your logic it if the CommandButton1 that shows the label2 and commandbutton2
provided that the combobox1 is on yes

Look at something like

Option Explicit :Option compare text

Private Sub ComboBox1_Change()
    Select Case ComboBox1.Value


    Case "Yes"
        UserForm1.Label2.Visible = True
        UserForm1.CommandButton2.Visible = True


    Case "No"
        UserForm1.Label2.Visible = False
        UserForm1.CommandButton2.Visible = False



    Case "cabbage"
        MsgBox " no cabbages today "



    Case Else
        MsgBox " invalid value"
    End Select


End Sub

 

[kevinivekevin]

Sorry for bumping this thread but I have a similar situation and after trying HarryS's solution it seems that the label will not reappear if I first select the option in the combobox that hides the label and then select the option that should unhide the label.

My combobox has many (~20) options to choose from and I only want to hide the label for 3 of them while the rest should keep the label unhidden.

So my sub is currently something like this:

Private Sub ComboBox1_Change()
Value = ComboBox1.Value

If Value = "1" Then
     [response]
End If

If Value = "2" Then
     [response]
End If

...

If Value = "20" Then
     [response]
End If

End Sub

What would be an efficient way to write it such that for 3 of the values the label would hide but for the rest the label would reappear/unhide?

Thanks in advance.

 

[My Aswer Is This]

I suggest you put your controls into "Frames" and then use a script like this:

Private Sub ComboBox2_Change()
Dim ans As Long
ans = ComboBox2.Value
Me.Controls("Frame" & ans).Visible = False
End Sub

 

[abmati]

We can put as under, it works:

Me.Controls("TextBox" & x).Visible = False
Me.Controls("Label" & x).Visible = False