Loop through controls on a Userform with Excel VBA
A Userform is a useful tool to get user input for your Excel VBA application. When the user clicks OK to close the form, it is usually necessary to check all input.
If there are many controls on the Userform, checking can be quite a job - especially if you check the controls one by one.
Quite often it is much easier to loop through the controls and take action depending of the control type and the user's input.
You can loop through the controls, because they are all members of the userform's controls collection. When you loop with "For Each...Next" it is important to know, that the sequence is the same as when you added the controls.
Here is an example that starts, when the user clicks the OK button. You can also download a spreadsheet with examples. The spreadsheet also shows, how you can distinguish between numeric values and text in textboxes.
Private Sub CommandButton1_Click()
Dim cCtl As Control
On Error GoTo ErrorHandle
For Each cCtl In Me.Controls
If TypeOf cCtl Is MSForms.CheckBox Then
If cCtl.Value = True Then
If TypeOf cCtl Is MSForms.TextBox Then
If TypeOf cCtl Is MSForms.OptionButton Then