Chapter 18
In This Chapter
Creating a dialog box: A hands-on example
Working with ListBox controls
Letting the user select a range from a UserForm
Showing a progress indicator for lengthy operations
Creating a stay-on-top dialog box
Displaying a chart in a UserForm
Presenting a handy checklist for creating dialog boxes
The previous chapters show you how to insert a UserForm (which contains a custom dialog box), add controls to the UserForm, and adjust some of the control’s properties. These skills, however, won’t do you much good unless you understand how to make use of UserForms in your VBA code. This chapter provides these missing details and presents some useful techniques and tricks in the process.
When you use a custom dialog box in your application, you normally write VBA code that does the following:
This example demonstrates the five points I describe in the preceding section. In this example, you use a dialog box to get two pieces of information: a person’s name and sex. The dialog box uses a TextBox control to get the name, and it uses three OptionButtons to get the sex (Male, Female, or Unknown). The information collected in the dialog box is then sent to the next blank row in a worksheet.
Figure 18-1 shows the finished UserForm for this example. For best results, start with a new workbook with only one worksheet in it. Then follow these steps:
In the Project window, select the empty workbook and choose Insert ⇒ UserForm.
An empty UserForm is added to the project.
Change the UserForm’s Caption property to Get Name and Sex.
If the Properties window isn’t visible, press F4.
This dialog box has eight controls:
Property |
Value |
Accelerator |
N |
Caption |
Name |
TabIndex |
0 |
Property |
Value |
Name |
TextName |
TabIndex |
1 |
Property |
Value |
Caption |
Sex |
TabIndex |
2 |
Property |
Value |
Accelerator |
M |
Caption |
Male |
Name |
OptionMale |
TabIndex |
0 |
Property |
Value |
Accelerator |
F |
Caption |
Female |
Name |
OptionFemale |
TabIndex |
1 |
Property |
Value |
Accelerator |
U |
Caption |
Unknown |
Name |
OptionUnknown |
TabIndex |
2 |
Value |
True |
Property |
Value |
Caption |
Enter |
Default |
True |
Name |
EnterButton |
TabIndex |
b |
Property |
Value |
Caption |
Close |
Cancel |
True |
Name |
CloseButton |
TabIndex |
4 |
If you’re following along on your computer (and you should be), take a few minutes to create this UserForm by using the preceding information. Make sure to create the Frame object before adding the OptionButtons to it.
In some cases, you may find copying an existing control easier than creating a new one. To copy a control, press Ctrl while you drag the control.
After you've added the controls to the UserForm, your next step is to develop some VBA code to display this dialog box:
Sub GetData()
UserForm1.Show
End Sub
This short procedure uses the UserForm object’s Show method to display the dialog box.
The next set of steps gives the user an easy way to execute the procedure:
Drag in the worksheet to create the button.
The Assign Macro dialog box appears.
Follow these steps to test your dialog box:
Click the Data Entry button on the worksheet, or click the Quick Access toolbar icon if you set one up.
The dialog box appears, as shown in Figure 18-2.
Click Enter or Close.
Nothing happens — which is understandable because you haven’t created any procedures yet.
In this section, I explain how to write the procedures that handle the events that occur when the dialog box is displayed. Follow these steps:
Double-click the Close button on the UserForm.
The VBE activates the Code window for the UserForm and provides an empty procedure named CloseButton_Click.
Private Sub CloseButton_Click()
Unload UserForm1
End Sub
This procedure, which is executed when the user clicks the Close button, simply unloads the dialog box from memory.
Private Sub EnterButton_Click()
Dim NextRow As Long
' Make sure Sheet1 is active
Sheets("Sheet1").Activate
' Determine the next empty row
NextRow = Application.WorksheetFunction. _
CountA(Range("A:A")) + 1
' Transfer the name
Cells(NextRow, 1) = TextName.Text
' Transfer the sex
If OptionMale Then Cells(NextRow, 2) = "Male"
If OptionFemale Then Cells(NextRow, 2) = "Female"
If OptionUnknown Then Cells(NextRow, 2) = "Unknown"
' Clear the controls for the next entry
TextName.Text = ""
OptionUnknown = True
TextName.SetFocus
End Sub
Now activate Excel and run the procedure again by clicking the Data Entry button.
The dialog box works just fine. Figure 18-3 shows how this looks in action. I added column headers in row 1, but that step is not necessary.
Here’s how the EnterButton_Click procedure works:
Play around with this routine some more, and you find that the macro has a small problem: It doesn’t ensure that the user actually enters a name in the TextBox. The following code — which is inserted into the EnterButton_Click procedure before the text is transferred to the worksheet — ensures that the user enters some text in the TextBox. If the TextBox is empty, a message appears, and the routine stops. The dialog box remains open, however, so the user can correct the problem.
' Make sure a name is entered
If TextName.Text = "" Then
MsgBox "You must enter a name."
Exit Sub
End If
After making these modifications, you find that the dialog box works flawlessly. In real life, you’d probably need to collect more information than just name and sex. However, the same basic principles apply. You just have to deal with more UserForm controls.
One more thing to remember: If the data doesn't begin in row 1 or if the data area contains any blank rows, the counting for the NextRow variable will be wrong. The COUNTA function is counting the number of cells in column A, and the assumption is that there are no blank cells above the last name in the column. Here's another way of determining the next empty row:
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
The statement simulates activating the last cell in column A, pressing End, pressing the up-arrow key, and then moving down one row. If you do that manually, the cell pointer will be in the next empty cell in column A — even if the data area doesn't begin in row 1 and contains blank rows.
I could probably fill an entire book with interesting and useful tips for working with custom dialog boxes, but this book has a limited number of pages, so I wrap it up with a few more examples.
ListBoxes are useful controls, but working with them can be a bit tricky. Before displaying a dialog box that uses a ListBox, you need to fill the ListBox with items. Then, when the dialog box is closed, you need to determine which item(s) the user selected.
To keep things simple, start with an empty workbook. The example in this section assumes the following:
Private Sub CancelButton_Click()
Unload UserForm1
End Sub
The following procedure is stored in the Initialize procedure for the UserForm. Follow these steps:
Select your UserForm, and press F7 to activate its Code window.
The VBE displays the Code window for your form and is ready for you to input the code for the Initialize event.
Sub UserForm_Initialize()
' Fill the list box
With ListBox1
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
End With
' Select the first list item
ListBox1.ListIndex = 0
End Sub
This initialization routine runs automatically whenever your UserForm is loaded. So when you use the Show method for the UserForm, the code is executed and your ListBox is populated with 12 items, each added via the AddItem method.
Sub ShowList()
UserForm1.Show
End Sub
The preceding code merely displays a dialog box with a ListBox filled with month names. What’s missing is a procedure to determine which item in the ListBox is selected.
Double-click the OKButton, and add the following OKButton_Click procedure:
Private Sub OKButton_Click()
Dim Msg As String
Msg = "You selected item # "
Msg = Msg & ListBox1.ListIndex
Msg = Msg & vbNewLine
Msg = Msg & ListBox1.Value
MsgBox Msg
Unload UserForm1
End Sub
This procedure displays a message box with the selected item number and the selected item.
If no item in the ListBox is selected, the ListIndex property returns –1. However, this will never be the case for this particular ListBox, because code in the UserForm_Initialize procedure selected the first item. It's impossible to unselect an item without selecting another item. So there will always be a selected item, if the user doesn't actually select a month.
Figure 18-4 shows how this looks.
This example is available at this book’s website.
If your ListBox is set up so that the user can select more than one item, you find that the ListIndex property returns only the last item selected. To determine all selected items, you need to use the Selected property, which contains an array.
UserForm1.ListBox1.MultiSelect = 1
The MultiSelect property has three possible settings. The meaning of each is shown in Table 18-1.
Table 18-1 Settings for the MultiSelect Property
Value |
VBA Constant |
Meaning |
0 |
fmMultiSelectSingle |
Only a single item can be selected. |
1 |
fmMultiSelectMulti |
Clicking an item or pressing the space bar selects or deselects an item in the list. |
2 |
fmMultiSelectExtended |
Items are added to or removed from the selection set by holding down the Shift or Ctrl key as you click items. |
The following procedure displays a message box that lists all selected items in a ListBox. Figure 18-5 shows an example.
Private Sub OKButton_Click()
Dim Msg As String
Dim i As Long
Dim Counter As Long
Msg = "You selected:" & vbNewLine
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
Counter = Counter + 1
Msg = Msg & ListBox1.List(i) & vbNewLine
End If
Next i
If Counter = 0 Then Msg = Msg & "(nothing)"
MsgBox Msg
Unload UserForm1
End Sub
This routine uses a For-Next loop to cycle through each item in the ListBox. Notice that the loop starts with item 0 (the first item) and ends with the last item (determined by the value of the ListCount property minus 1). If an item’s Selected property is True, it means that the list item was selected. The code also uses a variable (Counter) to keep track of how many items are selected. An If-Then statement modifies the message if nothing is selected.
In some cases, you may want the user to select a range while a dialog box is displayed. An example of this type of range selection occurs in the Create Table dialog box, which is displayed when you choose Home ⇒ Insert ⇒ Tables ⇒ Table. The Create Table dialog box has a range selector control that contains Excel's guess regarding the range to be converted — but you can use this control to change the range by selecting cells in the worksheet.
To allow a range selection in your dialog box, add a RefEdit control. The following example displays a dialog box with the current region’s range address displayed in a RefEdit control, as shown in Figure 18-6. The current region is the block of nonempty cells that contains the active cell. The user can accept or change this range. When the user clicks OK, the procedure makes the range bold.
This example assumes the following:
The code is stored in a VBA module and shown here. This code does two things: initializes the dialog box by assigning the current region’s address to the RefEdit control and displays the UserForm.
Sub BoldCells()
' Exit if worksheet is not active
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
' Select the current region
ActiveCell.CurrentRegion.Select
' Initialize RefEdit control
UserForm1.RefEdit1.Text = Selection.Address
' Show dialog
UserForm1.Show
End Sub
The following procedure is executed when the OK button is clicked. This procedure does some simple error checking to make sure that the range specified in the RefEdit control is valid.
Private Sub OKButton_Click()
On Error GoTo BadRange
Range(RefEdit1.Text).Font.Bold = True
Unload UserForm1
Exit Sub
BadRange:
MsgBox "The specified range is not valid."
End Sub
If an error occurs (most likely an invalid range specification in the RefEdit control), the code jumps to the BadRange label, and a message box is displayed. The dialog box remains open so the user can select another range.
Figure 18-7 shows a custom dialog box with three sets of OptionButtons. If your UserForm contains more than one set of OptionButtons, make sure that each set of OptionButtons works as a group. You can do so in either of two ways:
UserForm1.OptionButton1.Value = True
A SpinButton control lets the user specify a number by clicking arrows. This control consists only of arrows (no text), so you usually want a method to display the selected number. One option is to use a Label control, but this has a disadvantage: The user can’t type text in a Label. A better choice is to use a TextBox.
A SpinButton control and TextBox control form a natural pair, and Excel uses them frequently. For example, check out Excel's Page Setup dialog box for a few examples. Ideally, the SpinButton and its TextBox are always in sync: If the user clicks the SpinButton, the SpinButton’s value should appear in the TextBox. And if the user enters a value directly into the TextBox, the SpinButton should take on that value. Figure 18-8 shows a custom dialog box with a SpinButton and a TextBox.
This UserForm contains the following controls:
The event-handler procedure for the SpinButton follows. This procedure handles the Change event, which is triggered whenever the SpinButton value is changed. When the SpinButton’s value changes (when it’s clicked), this procedure assigns the SpinButton’s value to the TextBox. To create this procedure, double-click the SpinButton to activate the Code window for the UserForm. Then enter this code:
Private Sub SpinButton1_Change()
TextBox1.Text = SpinButton1.Value
End Sub
The event-handler for the TextBox, which is listed next, is a bit more complicated. To create this procedure, double-click the TextBox to activate the Code window for the UserForm. This procedure is executed whenever the user changes the text in the TextBox.
Private Sub TextBox1_Change()
Dim NewVal As Long
NewVal = Val(TextBox1.Text)
If NewVal >= SpinButton1.Min And _
NewVal <= SpinButton1.Max Then _
SpinButton1.Value = NewVal
End Sub
This procedure uses a variable, which stores the text in the TextBox (converted to a value with the Val function). It then checks to ensure that the value is within the proper range. If so, the SpinButton is set to the value in the TextBox. The net effect is that the SpinButton’s value is always equal to the value in the TextBox (assuming that the SpinButton's value is in the proper range).
If you have a macro that takes a long time to run, you might want to display a progress meter so people won't think Excel has crashed. You can use a UserForm to create an attractive progress indicator, as shown in Figure 18-9. Such a use of dialog boxes does, however, require a few tricks — which I’m about to show you.
The first step is to create your UserForm. In this example, the dialog box displays the progress while a macro inserts random numbers into 100 columns and 1,000 rows of the active worksheet. To create the dialog box, follow these steps:
Property |
Value |
Caption |
0% |
Name |
FrameProgress |
SpecialEffect |
2 — fmSpecialEffectSunken |
Width |
204 |
Height |
28 |
Property |
Value |
Name |
LabelProgress |
BackColor |
&H0000C000& (green) |
Caption |
(no caption) |
SpecialEffect |
1 — fmSpecialEffectRaised |
Width |
20 |
Height |
13 |
Top |
5 |
Left |
2 |
Add another Label above the frame, and change its caption to Entering random numbers…
The UserForm should resemble Figure 18-10.
This example uses two procedures and a module-level variable:
Dim ProgressIndicator as UserForm1
Sub EnterRandomNumbers ()
' Inserts random numbers on the active worksheet
Dim Counter As Long
Dim RowMax As Long, ColMax As Long
Dim r As Long, c As Long
Dim PctDone As Single
' Create a copy of the form in a variable
Set ProgressIndicator = New UserForm1
' Show ProgressIndicator in modeless state
ProgressIndicator.Show vbModeless
If TypeName(ActiveSheet) <> "Worksheet" Then
Unload ProgressIndicator
Exit Sub
End If
' Enter the random numbers
Cells.Clear
Counter = 1
RowMax = 200
ColMax = 50
For r = 1 To RowMax
For c = 1 To ColMax
Cells(r, c) = Int(Rnd * 1000)
Counter = Counter + 1
Next c
PctDone = Counter / (RowMax * ColMax)
Call UpdateProgress(PctDone)
Next r
Unload ProgressIndicator
Set ProgressIndicator = Nothing
End Sub
Sub UpdateProgress(pct)
With ProgressIndicator
.FrameProgress.Caption = Format(pct, "0%")
.LabelProgress.Width = pct * (.FrameProgress _
.Width - 10)
End With
' The DoEvents statement is responsible for the form updating
DoEvents
End Sub
When the EnterRandomNumbers procedure is executed, it loads a copy of UserForm1 into the module variable named ProgressIndicator. Then it sets the width of the LabelProgress label to 0 and displays the UserForm in modeless state (so the code will continue to run).
The EnterRandomNumber procedure checks the active sheet. If it’s not a worksheet, the UserForm (ProgressIndicator) is closed, and the procedure ends with no action. If the active sheet is a worksheet, the procedure does the following:
Using a progress indicator will, of course, make your macro run a bit slower because the code is doing additional work updating the UserForm. If speed is absolutely critical, think twice about using a progress indicator.
Tabbed dialog boxes are useful because they let you present information in small, organized chunks. Excel's Format Cells dialog box (which is displayed when you right-click a cell and choose Format Cells) is a good example. The dialog box in this example uses three tabs to help organize some of Excel's display options.
Creating your own tabbed dialog boxes is relatively easy, thanks to the MultiPage control. Figure 18-11 shows a custom dialog box that uses a MultiPage control with three pages, or tabs. When the user clicks a tab, a new page is activated, and only the controls on that page are displayed.
Notice that this is a modeless dialog box. In other words, the user can keep it displayed while working. Each of the controls has an immediate effect, so there is no need to have an OK button. Here's the procedure that displays the UserForm so it stays on top:
Sub ShowDialog()
UserForm1.Show vbModeless
End Sub
Modeless UserForms work a bit differently in Excel 2013 and Excel 2016, due to the single document interface (every workbook displays in its own separate window). With previous versions of Excel, a modeless UserForm stays on top regardless of which workbook is active. In Excel 2013 and later, a modeless UserForm only stays on top of the workbook window that was active when the UserForm was displayed.
If you need to display a chart in a UserForm, you find that Excel doesn’t provide any direct way to do so. Therefore, you need to get creative. This section describes a technique that lets you display one or more charts in a UserForm.
Figure 18-12 shows an example that displays three charts. The UserForm has an Image control. The trick is to use VBA code to save the chart as a GIF file and then specify that file as the Image control’s Picture property (which loads the image from your disk). The Previous and Next buttons switch the displayed chart.
Private Sub UpdateChart()
Dim CurrentChart As Chart
Dim Fname As String
Set CurrentChart = _
Sheets("Charts").ChartObjects(ChartNum).Chart
CurrentChart.Parent.Width = 300
CurrentChart.Parent.Height = 150
' Save chart as GIF
Fname = ThisWorkbook.Path & " emp.gif"
CurrentChart.Export FileName:=Fname, FilterName:="GIF"
' Show the chart
Image1.Picture = LoadPicture(Fname)
End Sub
This procedure determines a name for the saved chart and then uses the Export method to export the GIF file. Finally, it uses the VBA LoadPicture function to specify the Picture property of the Image object.
I wrap up this chapter with a checklist for use when creating dialog boxes:
The best way to master custom dialog boxes is to create dialog boxes — lots of them. Start simply and experiment with the controls and their properties. And don’t forget about the Help system; it’s your best source for details about every control and property.