Controls Collection in Excel VBA
When creating Userforms in Excel VBA, you can use the Controls collection to easily loop through controls and set a property of each control to a specific value.
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, text 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 text box control by clicking on TextBox from the Toolbox. Next, you can drag a text box on the Userform.
4. To change the caption of the Userform, label 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 vbModeless
End Sub
Explanation: by adding vbModeless, you can use the Userform and work in your worksheet at the same time.
We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we click the command button on the Userform.
6. Open the Visual Basic Editor.
7. In the Project Explorer, double click on UserForm1.
8. Double click on the command button.
9. Add the following code lines:
Dim i As Integer
For i = 1 To 10
Controls("TextBox" & i).Value = Cells(i + 1, 1).Value
Next i
End Sub
Explanation: With just a few lines of code, we can fill the text boxes with the phone numbers from the sheet. We used the & operator to concatenate (join) the elements. These code lines work because we didn't change the names of the text box controls (TextBox1, TextBox2, TextBox3, etc). To change the names of the controls, click View, Properties Window and click on each control.
Result when you click the command button on the sheet:
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.
