Loop through Controls in Excel VBA
With just a few lines of code, we can easily loop through controls on an Excel VBA Userform. The Userform we are going to create looks as follows:
To create this Userform, execute the following steps.
1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.
2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.
3. Add the label, check boxes (first at the top, the second below the first, and so on) and command button. Once this has been completed, the result should be consistent with the picture of the Userform shown earlier. For example, create a check box control by clicking on CheckBox from the Toolbox. Next, you can drag a check box on the Userform.
4. You can change the names and the captions of the controls. Names are used in the Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of the controls, but it is not necessary here because we only have a few controls in this example. To change the caption of the Userform, label, check boxes and command button, click View, Properties Window and click on each control.
5. To show the Userform, place a command button on your worksheet and add the following code line:
UserForm1.Show
End Sub
We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we click on the Clear button.
6. Open the Visual Basic Editor.
7. In the Project Explorer, double click on UserForm1.
8. Double click on the Clear button.
9. Add the following code lines:
Dim contr As Control
For Each contr In UserForm1.Controls
If TypeName(contr) = "CheckBox" Then
contr.Value = False
End If
Next
End Sub
Explanation: Excel VBA loops through all controls on the Userform. The TypeName function is used to filter out the check box controls. If a check box control, Excel VBA unchecks the check box. contr is randomly chosen here, you can use any name. Remember to refer to this name in the rest of your code.
Result when you click the Clear button:
If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Data Analysis in Excel and Excel VBA.
