Search code examples
comboboxuserformshortcut

Excel: Set comboboxes 3-8 .visible to false if ComboBox 1 is empty

I have a UserForm with ComboBoxes 1-8 that each pick up text in designated cells in the ws upon UserForm_Activate. I have set ComboBoxes 2-8 to .Visible=False if ComboBox1.Value = "".

Is there a way to use an abbreviated code to set .Visible=False for each ComboBox without listing each one separately? I have added below what I'm using now, but I create forms like this often and would rather use a "Dim i as Integer / For i =" type thing instead that I could just copy and paste where needed. Thank you in advance!

If ComboBox1.Value = "" Then
ComboBox2.Visible = False
ComboBox3.Visible = False
ComboBox4.Visible = False
ComboBox5.Visible = False
ComboBox6.Visible = False
ComboBox7.Visible = False
ComboBox8.Visible = False
Solution

  • Indirect referencing of controls

    Use indirect referencing via Controls() and try

    Dim i As Long, current As Long
current = 1             ' << change to the combobox to be excepted
For i = 1 to 8          ' loop through all comboboxes
    Me.Controls("ComboBox" & i).Visible = False
Next i
Me.Controls("ComboBox" & current).Visible = True