Table of Contents
Controls Collection
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:
Private Sub CommandButton1_Click()
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:
Private Sub CommandButton1_Click()
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:
