Loop Through Controls on a UserForm
Excel VBA: Loop Through Controls on a UserForm. Textbox, ComboBox, CheckBox etc
Got any Excel Questions? Free Excel Help
Loop Through UserForm Controls
Excel VBA UserForms and their associated controls are a great way to present/collect data from users. There are often occasions however when we need to loop through all controls on a UserForm, or only certain specified controls.
Loop Through All Controls
Use the code below to loop through all Controls on a UserForm
Private Sub CommandButton1_Click()
Dim cCont As Control
For Each cCont In Me.Controls
'DO STUFF HERE
Next cCont
End Sub
Loop Through Specific/Specified Controls
Use the code below to loop through only specified Controls on a UserForm.
Private Sub CommandButton1_Click()
Dim cCont As Control
For Each cCont In Me.Controls
If TypeName(cCont) = "TextBox" Then
'DO STUFF HERE
End If
Next cCont
End Sub
Loop Through Specific Controls on a Specified Page of a MultiPage Control
Use the code below to loop through only specific Controls on a specified page of a MultiPage Control. Note that Pages(0) is always the first page of any MultiPage Control.
Private Sub CommandButton1_Click()
Dim cCont As Control
For Each cCont In Me.MultiPage1.Pages(0).Controls
If TypeName(cCont) = "TextBox" Then
'DO STUFF HERE
End If
Next cCont
End Sub
Loop Through Specified Controls on all Pages of a MultiPage Control
Use the code below to loop through specified controls on all pages of a MultiPage Control.
Private Sub CommandButton1_Click()
Dim pPage As Page, cCont As Control
For Each pPage In Me.MultiPage1.Pages
For Each cCont In pPage.Controls
If TypeName(cCont) = "ComboBox" Then
'DO STUFF HERE
End If
Next cCont
Next pPage
End Sub
See also:
Free Training Course: Lesson 1 - Excel Fundamentals
See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
