Denne side på dansk Home > Excel VBA macros > Loop controls on a Userform

RSS

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

'We loop the form's controls
For Each cCtl In Me.Controls
   'If it is a checkbox
   If TypeOf cCtl Is MSForms.CheckBox Then
      If cCtl.Value = True Then
         'Action
      End If
   End If
   'If it is a tekstbox
   If TypeOf cCtl Is MSForms.TextBox Then
      'Action
   End If
   'If it is an optionbutton
   If TypeOf cCtl Is MSForms.OptionButton Then
      'Action
   End If
   'Etc.
Next

Exit Sub
ErrorHandle:
MsgBox Err.Description
End Sub

Related: