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

Question

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

Posted an answer; btw it makes no difference to use Long instead of Integer (especially using row or column indices you are on the safe side :) @ — T.M.

T.M. T.M. · Accepted Answer · 2020-02-23T18:18:54

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

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

1 Answers