Basics of VBA / Macros Chapter – 87 (Userform Part-8)

Loop through Controls

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:

Loop through Controls in Excel VBA

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.

Userform Screen Setup in Excel VBA

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:

Private Sub CommandButton1_Click()

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:

Private Sub CommandButton1_Click()

Dim contr As control

For Each contr In UserForm1.Controls
If TypeName(contr) = “CheckBox” Then
contr.Value = False
End If
Next

End Sub

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:

Loop through Controls Result

Share This:

Basics of VBA / Macros Chapter – 86 (Userform Part-7)

Dependent Combo Boxes

Below we will look at a program in Excel VBA which creates a Userform that contains dependent combo boxes. The Userform we are going to create looks as follows. The user selects Animals from a drop-down list. As a result, the user can select an animal from a second drop-down list.

Dependent Combo Boxes in Excel VBA

The user selects Sports from a drop-down list. As a result, the user can select a sport from a second drop-down list.

Dependent Combo Boxes in Excel VBA

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.

Userform Screen Setup in Excel VBA

3. Add the combo boxes (first at the left, the second at the right) 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 combo box control by clicking on ComboBox from the Toolbox. Next, you can drag a combo 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 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

End Sub

We are now going to create the Sub UserForm_Initialize. When you use the Show method for the Userform, this sub will automatically be executed.

6. Open the Visual Basic Editor.

7. In the Project Explorer, right click on UserForm1 and then click View Code.

8. Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list.

9. Add the following code lines:

Private Sub UserForm_Initialize()

With ComboBox1
.AddItem “Animals”
.AddItem “Sports”
.AddItem “Food”
End With

End Sub

These code lines fill the first combo box. We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we select an item from the first combo box.

10. In the Project Explorer, double click on UserForm1.

11. Double click on the first combo box.

12. Add the following code lines:

Private Sub ComboBox1_Change()

Dim index As Integer
index = ComboBox1.ListIndex

ComboBox2.Clear

Select Case index
Case Is = 0
With ComboBox2
.AddItem “Dog”
.AddItem “Cat”
.AddItem “Horse”
End With
Case Is = 1
With ComboBox2
.AddItem “Tennis”
.AddItem “Swimming”
.AddItem “Basketball”
End With
Case Is = 2
With ComboBox2
.AddItem “Pancakes”
.AddItem “Pizza”
.AddItem “Chinese”
End With
End Select

End Sub

Excel VBA uses the value of the variable index to test each subsequent Case statement to see with which items the second combo box should be filled. Go through our Select Case program to learn more about the Select Case structure.

13. Double click on the Import button.

14. Add the following code line:

Private Sub CommandButton1_Click()

Range(“A1”).Value = ComboBox2.Value

End Sub

Result:

Dependent Combo Boxes in Excel VBA

Share This:

Basics of VBA / Macros Chapter – 85 (Userform Part-6)

Multicolumn Combo Box

Below we will look at a program in Excel VBA which creates a Userform that contains a multicolumn combo box. The Userform we are going to create looks as follows:

Multicolumn Combo Box in Excel VBA

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.

Userform Screen Setup in Excel VBA

3. Add the label, combo box and command buttons. Once this has been completed, the result should be consistent with the picture of the Userform shown earlier. For example, create a combo box control by clicking on ComboBox from the Toolbox. Next, you can drag a combo 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 and command buttons, 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

End Sub

We are now going to create the Sub UserForm_Initialize. When you use the Show method for the Userform, this sub will automatically be executed.

6. Open the Visual Basic Editor.

7. In the Project Explorer, right click on UserForm1 and then click View Code.

8. Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list.

9. Add the following code lines:

Private Sub UserForm_Initialize()

ComboBox1.ColumnCount = 2

Dim Films(1 To 5, 1 To 2) As String
Dim i As Integer, j As Integer

Films(1, 1) = “Lord of the Rings”
Films(2, 1) = “Speed”
Films(3, 1) = “Star Wars”
Films(4, 1) = “The Godfather”
Films(5, 1) = “Pulp Fiction”

Films(1, 2) = “Adventure”
Films(2, 2) = “Action”
Films(3, 2) = “Sci-Fi”
Films(4, 2) = “Crime”
Films(5, 2) = “Drama”

ComboBox1.List = Films

End Sub

The first code line sets the number of columns of the combo box to 2. Instead of setting the number of columns at runtime, you can also configure this setting at design time. To achieve this, right mouse click on the combo box control, click Properties and set the ColumnCount property to 2. Next, we declare and initialize a two-dimensional array. The last code line assigns the array to the combo box. We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we click the command buttons on the Userform.

10. In the Project Explorer, double click on UserForm1.

11. Double click on the OK button.

12. Add the following code lines:

Private Sub CommandButton1_Click()

Unload Me

MsgBox “You selected ” & ComboBox1.Value

On Error Resume Next
MsgBox “You like ” & ComboBox1.Column(1) & ” movies”

End Sub

These code lines close the Excel VBA Userform and display the selected item and genre. The ‘On Error Resume Next’ statement ignores the error when the user fills in his/her own movie (in this case there is no genre available).

13. Double click on the Cancel button.

14. Add the following code line:

Private Sub CommandButton2_Click()

Unload Me

End Sub

Result when you select Speed and click OK.

Multicolumn Combo Box Result

Multicolumn Combo Box Result

Share This: