Basics of VBA / Macros Chapter – 90 (Userform Part-11)

Interactive Userform

Below we will look at a program in Excel VBA that creates an interactive Userform. The Userform we are going to create looks as follows:

Excel VBA Interactive Userform

Explanation: whenever you enter a value in the ID text box, Excel VBA loads the corresponding record. When you click the Edit / Add button, Excel VBA edits the record on the sheet or adds the record when the ID does not yet exist. The Clear button clears all the text boxes. The Close button closes the Userform. 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 labels, text boxes (first at the top, the second below the first, and so on) 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 text box control by clicking on TextBox from the Toolbox. Next, you can drag a text 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 labels, text boxes 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 line:

Private Sub UserForm_Initialize()

TextBox1.SetFocus

End Sub

This code line sets the focus on the first text box as this is where we want to start when the Userform is loaded. We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we enter a value in the ID text box or when we click one of the command buttons.

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

11. Choose TextBox1 from the left drop-down list. Choose Change from the right drop-down list.

12. Add the following code line:

Private Sub TextBox1_Change()

GetData

End Sub

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

14. Double click on the Edit / Add command button.

15. Add the following code line:

Private Sub CommandButton1_Click()

EditAdd

End Sub

16. Double click on the Clear command button.

17. Add the following code line:

Private Sub CommandButton2_Click()

ClearForm

End Sub

These subs call other subs we are going to create in a second.

18. Double click on the Close command button.

19. Add the following code line:

Private Sub CommandButton3_Click()

Unload Me

End Sub

This code line closes the Userform. Time to create the subs. You can go through our Function and Sub chapter to learn more about subs. If you are in a hurry, simply place the following subs into a module (In the Visual Basic Editor, click Insert, Module).

20. First, declare three variables of type Integer and one variable of type Boolean. Declare the variables in the General Declarations section (at the top of the module). This way you only have to declare the variables once and you can use them in multiple subs.

Dim id As Integer, i As Integer, j As Integer, flag As Boolean

21. Add the GetData sub.

Sub GetData()

If IsNumeric(UserForm1.TextBox1.Value) Then
flag = False
i = 0
id = UserForm1.TextBox1.Value

Do While Cells(i + 1, 1).Value <> “”

If Cells(i + 1, 1).Value = id Then
flag = True
For j = 2 To 3
UserForm1.Controls(“TextBox” & j).Value = Cells(i + 1, j).Value
Next j
End If

i = i + 1

Loop

If flag = False Then
For j = 2 To 3
UserForm1.Controls(“TextBox” & j).Value = “”
Next j
End If

Else
ClearForm
End If

End Sub

If the ID text box contains a numeric value, Excel VBA searches for the ID and loads the corresponding record. We use the Controls Collection to easily loop through text boxes. If Excel VBA cannot find the ID (flag is still False), it empties the second and third text box. If the ID text box does not contain a numeric value, Excel VBA calls the ClearForm sub.

22. Add the ClearForm sub.

Sub ClearForm()

For j = 1 To 3
UserForm1.Controls(“TextBox” & j).Value = “”
Next j

End Sub

Excel VBA clears all the text boxes.

23. Add the EditAdd sub.

Sub EditAdd()

Dim emptyRow As Long

If UserForm1.TextBox1.Value <> “” Then
flag = False
i = 0
id = UserForm1.TextBox1.Value
emptyRow = WorksheetFunction.CountA(Range(“A:A”)) + 1

Do While Cells(i + 1, 1).Value <> “”

If Cells(i + 1, 1).Value = id Then
flag = True
For j = 2 To 3
Cells(i + 1, j).Value = UserForm1.Controls(“TextBox” & j).Value
Next j
End If

i = i + 1

Loop

If flag = False Then
For j = 1 To 3
Cells(emptyRow, j).Value = UserForm1.Controls(“TextBox” & j).Value
Next j
End If

End If

End Sub

If the ID text box is not empty, Excel VBA edits the record on the sheet (the opposite of loading a record as we have seen earlier). If Excel VBA cannot find the ID (flag is still False), it adds the record to the next empty row. The variable emptyRow is the first empty row and increases every time a record is added.

24. Exit the Visual Basic Editor, enter the labels shown below into row 1 and test the Userform.

Excel VBA Interactive Userform

Share This:

Basics of VBA / Macros Chapter – 89 (Userform Part-10)

Userform with Multiple Pages

Below we will look at a program in Excel VBA which creates a Userform that contains multiple pages. This userform also contains images. The Multipage Control contains two pages. At page 1, the user can fill in his/her personal information. At page 2, the user can indicate which painting he/she likes the most.

Userform Page 1 Userform Page 2

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 Multipage control, labels, text boxes (first at the top, the second below the first), frame, option buttons (first at the left, the second at the right), list box, Image control and command button. Once this has been completed, the result should be consistent with the empty version of the Userform shown earlier. For example, create a Multipage control by clicking on Multipage from the Toolbox. Next, you can drag a Multipage control on the Userform. When you arrive at the Gender frame, remember to draw this frame first before you place the two option buttons in it.

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, Multipage tabs, labels, frame, option buttons 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 ListBox1
.AddItem “Mountains”
.AddItem “Sunset”
.AddItem “Beach”
.AddItem “Winter”
End With

End Sub

The list box on page 2 will be filled. 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 list box or when we click on the OK button.

10. Download the images (right side of this page) and add them to “C:\test\”

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

12. Double click on the list box.

13. Add the following code lines:

Private Sub ListBox1_Click()

If ListBox1.ListIndex = 0 Then
Image1.Picture = LoadPicture(“C:\test\Mountains.jpg”)
End If

If ListBox1.ListIndex = 1 Then
Image1.Picture = LoadPicture(“C:\test\Sunset.jpg”)
End If

If ListBox1.ListIndex = 2 Then
Image1.Picture = LoadPicture(“C:\test\Beach.jpg”)
End If

If ListBox1.ListIndex = 3 Then
Image1.Picture = LoadPicture(“C:\test\Winter.jpg”)
End If

End Sub

These code lines load a picture depending on the selected item in the list box.

14. Double click on the OK button.

15. Add the following code lines:

Private Sub CommandButton1_Click()

Dim emptyRow As Long

‘Make Sheet1 active
Sheet1.Activate

‘Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range(“A:A”)) + 1

‘Transfer information
Cells(emptyRow, 1).Value = TextBox1.Value
Cells(emptyRow, 2).Value = TextBox2.Value

If OptionButton1.Value = True Then
Cells(emptyRow, 3).Value = “Male”
Else
Cells(emptyRow, 3).Value = “Female”
End If

Cells(emptyRow, 4).Value = ListBox1.Value

‘Close Userform
Unload Me

End Sub

First, we activate Sheet1. Next, we determine emptyRow. The variable emptyRow is the first empty row and increases every time a record is added. Next, we transfer the information from the Userform to the specific columns of emptyRow. Finally, we close the Userform.

16. Exit the Visual Basic Editor, enter the labels shown below into row 1 and test the Userform.

Result:

Test the Userform

Share This:

Basics of VBA / Macros Chapter – 88 (Userform Part-9)

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:

Controls Collection 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, 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

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

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:

Controls Collection Result

Share This: