VBA loop thru consecutively numbered names

user_9220700 0
Viewed 56

I know how to loop through numbers in parentheses like 

For i = 0 To (ComboBox4.ListCount - 1)
If ComboBox4.Value = ComboBox4.List(i) Then inList = True
Next i

But how can I loop through numbers that are not in parentheses? Like consecutively numbered names:

Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""

I tried: 

for i=1 to 3
"Me.ComboBox"&i&".Value" = ""
next i

But that does not work. How should it look like?

for-loop vba
2 Answers

You'd use the Controls collection.

E.g.:

Me.Controls("ComboBox" & i)

Edit:
As the controls on a form are part of a collection you can also iterate through the collection pulling each control in turn: 

    Private Sub UserForm_Initialize()

        Dim ctl As Control

        For Each ctl In Me.Controls
            If TypeOf ctl Is MSFORMS.ComboBox Then
'           If TypeName(ctl) = "ComboBox" Then
                MsgBox ctl.Name
            End If
        Next ctl

    End Sub

I've added two ways to identify the control type as suggested by @CallumDA.

Microsoft writes:

  • The TypeName function returns a string and is the best choice when you need to store or display the class name of an object.
  • The TypeOf...Is operator is the best choice for testing an object's type, because it is much faster than an equivalent string comparison using TypeName.
user_46773050

You can use the controls-function:

Me.Controls("ComboBox" & i).Value = 'your code...
user_105920830
Related