Chapter 14: UserForm Examples
IN THIS CHAPTER
• Using a UserForm for a simple menu
• Selecting ranges from a UserForm
• Using a UserForm as a splash screen
• Changing the size of a UserForm while it's displayed
• Zooming and scrolling a sheet from a UserForm
• Understanding various techniques that involve a ListBox control
• Using an external control
• Using the MultiPage control
• Animating a Label control
Creating a UserForm “Menu”
Sometimes, you might want to use a UserForm as a type of menu. In other words, the UserForm presents some options, and the user makes a choice. This section presents two ways to do this: using CommandButtons or using a ListBox.
Chapter 15 contains additional examples of more advanced UserForm techniques.
Using CommandButtons in a UserForm
Figure 14-1 shows an example of a UserForm that uses CommandButton controls as a simple menu. Setting up this sort of UserForm is easy, and the code behind the UserForm is straightforward. Each CommandButton has its own event-handler procedure. For example, the following procedure is executed when CommandButton1 is clicked:
FIGURE 14-1: This dialog box uses CommandButtons as a menu.
Private Sub CommandButton1_Click()
Me.Hide
Call Macro1
Unload Me
End Sub
This procedure hides the UserForm, calls Macro1, and then closes the UserForm. The other buttons have similar event-handler procedures.
Using a ListBox in a UserForm
Figure 14-2 shows another example that uses a ListBox as a menu. Before the UserForm is displayed, its Initialize event-handler procedure is called. This procedure, which follows, uses the AddItem method to add six items to the ListBox:
FIGURE 14-2: This dialog box uses a ListBox as a menu.
Private Sub UserForm_Initialize()
With ListBox1
.AddItem “Macro1”
.AddItem “Macro2”
.AddItem “Macro3”
.AddItem “Macro4”
.AddItem “Macro5”
.AddItem “Macro6”
End With
End Sub
The Execute button also has a procedure to handle its Click event:
Private Sub ExecuteButton_Click()
Select Case ListBox1.ListIndex
Case -1
MsgBox “Select a macro from the list.”
Exit Sub
Case 0: Call Macro1
Case 1: Call Macro2
Case 2: Call Macro3
Case 3: Call Macro4
Case 4: Call Macro5
Case 5: Call Macro6
End Select
Unload Me
End Sub
This procedure accesses the ListIndex property of the ListBox to determine which item is selected. The procedure uses a Select Case structure to execute the appropriate macro. If the ListIndex is –1, nothing is selected in the ListBox, and the user sees a message.
In addition, this UserForm has a procedure to handle the double-click event for the ListBox. Double-clicking an item in the ListBox executes the corresponding macro.
The two examples in this section are available on the companion CD-ROM. The filename is userform menus.xlsm.
Chapter 15 shows a similar example in which you can use a UserForm to simulate a toolbar.
Selecting Ranges from a UserForm
Many of Excel's built-in dialog boxes allow the user to specify a range. For example, the Goal Seek dialog box (displayed by choosing Data⇒Data Tools⇒What-If Analysis⇒Goal Seek) asks the user to select two single-cell ranges. The user can either type the range addresses (or names) directly or use the mouse to point and click in a sheet to make a range selection.
Your UserForms can also provide this type of functionality, thanks to the RefEdit control. The RefEdit control doesn't look exactly like the range selection control used in Excel's built-in dialog boxes, but it works in a similar manner. If the user clicks the small button on the right side of the control, the dialog box disappears temporarily, and a small range selector is displayed — which is exactly what happens with Excel's built-in dialog boxes.
Unfortunately, the RefEdit control has a few quirks that still haven't been fixed. You'll find that this control doesn't allow the user to use shortcut range selection keys (for example, pressing End, followed by Shift+↓ will not select cells to the end of the column). In addition, after clicking the small button on the right side of the control (to temporarily hide the dialog box), you're limited to mouse selections only. You can't use the keyboard at all to make a selection.
Figure 14-3 shows a UserForm that contains a RefEdit control. This dialog box enables the user to perform a simple mathematical operation on all nonformula (and non-empty) cells in the selected range. The operation that's performed corresponds to the selected OptionButton.
This example is available on the companion CD-ROM in a file named range selection demo.xlsm.
FIGURE 14-3: The RefEdit control shown here allows the user to select a range.
Following are a few things to keep in mind when using a RefEdit control:
• The RefEdit control returns a text string that represents a range address. You can convert this string to a Range object by using a statement such as
Set UserRange = Range(RefEdit1.Text)
• Initializing the RefEdit control to display the current range selection is good practice. You can do so in the UserForm_Initialize procedure by using a statement such as
RefEdit1.Text = ActiveWindow.RangeSelection.Address
• For best results, avoid using a RefEdit control inside of a Frame or a MultiPage control. Doing so may cause Excel to crash.
• Don't assume that RefEdit will always return a valid range address. Pointing to a range isn't the only way to get text into this control. The user can type any text and can also edit or delete the displayed text. Therefore, you need to make sure that the range is valid. The following code is an example of a way to check for a valid range. If an invalid range is detected, the user is given a message, and focus is set to the RefEdit control so that the user can try again.
On Error Resume Next
Set UserRange = Range(RefEdit1.Text)
If Err.Number <> 0 Then
MsgBox “Invalid range selected”
RefEdit1.SetFocus
Exit Sub
End If
On Error GoTo 0
• The user can also click the worksheet tabs while selecting a range with the RefEdit control. Therefore, you can't assume that the selection is on the active sheet. However, if a different sheet is selected, the range address is preceded by a sheet name. For example:
Sheet2!$A$1:$C$4
• If you need to get a single cell selection from the user, you can pick out the upper-left cell of a selected range by using a statement such as
Set OneCell = Range(RefEdit1.Text).Range(“A1”)
As I discuss in Chapter 12, you can also use Excel's InputBox method to allow the user to select a range.
Creating a Splash Screen
Some developers like to display some introductory information when the application is opened. This display is commonly known as a splash screen. You're undoubtedly familiar with Excel's splash screen, which appears for a few seconds when Excel is loading.
You can create a splash screen for your Excel application with a UserForm. This example is essentially a UserForm that displays automatically and then dismisses itself after five seconds.
The companion CD-ROM contains a workbook that demonstrates this procedure. The file is named splash screen.xlsm.
Follow these instructions to create a splash screen for your project:
1. Create your workbook.
2. Activate the Visual Basic Editor (VBE) and insert a new UserForm into the project.
The code in this example assumes that this form is named UserForm1.
3. Place any controls that you like on UserForm1.
For example, you may want to insert an Image control that has your company's logo. Figure 14-4 shows an example.
FIGURE 14-4: This splash screen is displayed briefly when the workbook is opened.
4. Insert the following procedure into the code module for the ThisWorkbook object:
Private Sub Workbook_Open()
UserForm1.Show
End Sub
5. Insert the following procedure into the code module for UserForm1.
For a delay other than five seconds, change the argument for the TimeValue function.
Private Sub UserForm_Activate()
Application.OnTime Now + _
TimeValue(“00:00:05”), “KillTheForm”
End Sub
6. Insert the following procedure into a general VBA module:
Private Sub KillTheForm()
Unload UserForm1
End Sub
When the workbook is opened, the Workbook_Open procedure is executed. The procedure in Step 4 displays the UserForm. At that time, the UserForm's Activate event occurs, which triggers the UserForm_Activate procedure (see Step 5). This procedure uses the OnTime method of the Application object to execute a procedure named KillTheForm at a particular time. In this case, the time is five seconds after the activation event. The KillTheForm procedure simply unloads the UserForm.
7. As an option, you can add a small CommandButton named CancelButton, set its Cancel property to True, and insert the following event-handler procedure in the UserForm's code module:
Private Sub CancelButton_Click()
Unload Me
End Sub
Doing so lets the user cancel the splash screen before the time has expired by pressing Esc. In the example, I placed this small button behind another object so that it's not visible.
Keep in mind that the splash screen isn't displayed until the workbook is entirely loaded. In other words, if you'd like to display the splash screen to give the user something to look at while the workbook is loading, this technique won't fill the bill.
If your application needs to run some VBA procedures at start-up, you can display the UserForm modeless so that the code will continue running while the UserForm is displayed. To do so, change the Workbook_Open procedure as follows:
Private Sub Workbook_Open()
UserForm1.Show vbModeless
‘ other code goes here
End Sub
Disabling a UserForm's Close Button
When a UserForm is displayed, clicking the Close button (the X in the upper-right corner) will unload the form. You might have a situation in which you don't want the Close button to unload the form. For example, you might require that the UserForm be closed only by clicking a particular CommandButton.
Although you can't actually disable the Close button, you can prevent the user from closing a UserForm by clicking it. You can do so by monitoring the UserForm's QueryClose event.
The following procedure, which is located in the code module for the UserForm, is executed before the form is closed (that is, when the QueryClose event occurs):
Private Sub UserForm_QueryClose _
(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
MsgBox “Click the OK button to close the form.”
Cancel = True
End If
End Sub
The UserForm_QueryClose procedure uses two arguments. The CloseMode argument contains a value that indicates the cause of the QueryClose event. If CloseMode is equal to vbFormControlMenu (a built-in constant), that means that the user clicked the Close button. If a message is displayed, the Cancel argument is set to True, and the form isn't actually closed.
The example in this section is available on the companion CD-ROM in a file named queryclose demo.xlsm.
Keep in mind that a user can press Ctrl+Break to break out of the macro. In this example, pressing Ctrl+Break while the UserForm is displayed causes the UserForm to be dismissed. To prevent this occurrence, execute the following statement prior to displaying the UserForm:
Make sure that your application is debugged before you add this statement. Otherwise, you'll find that it's impossible to break out of an accidental endless loop.
Changing a UserForm's Size
Many applications use dialog boxes that change their own size. For example, Excel's Find and Replace dialog box (displayed when you choose Home⇒Editing⇒Find & Select⇒Replace) increases its height when the user clicks the Options button.
The example in this section demonstrates how to get a UserForm to change its size dynamically. Changing a dialog box's size is done by altering the Width or Height property of the UserForm object.
Refer to Chapter 15 for an example that allows the user to change the UserForm's size by dragging the lower-right corner.
Figure 14-5 shows the dialog box as it is first displayed, and Figure 14-6 shows it after the user clicks the Options button. Notice that the button's caption changes, depending on the size of the UserForm.
FIGURE 14-5: A sample dialog box in its standard mode.
FIGURE 14-6: The same dialog box enlarged to show some options.
While you're creating the UserForm, set it to its largest size to enable you to work with the controls. Then use the UserForm_Initialize procedure to set it to its default (smaller) size.
The code uses two constants, defined at the top of the module:
Const SmallSize As Integer = 124
Const LargeSize As Integer = 164
This example displays a list of worksheets in the active workbook and lets the user select which sheets to print. Following is the event handler that's executed when the CommandButton named OptionsButton is clicked:
Private Sub OptionsButton_Click()
If OptionsButton.Caption = “Options >>” Then
Me.Height = LargeSize
OptionsButton.Caption = “<< Options”
Else
Me.Height = SmallSize
OptionsButton.Caption = “Options >>”
End If
End Sub
This procedure examines the Caption of the CommandButton and sets the UserForm's Height property accordingly.
When controls aren't displayed because they're outside the visible portion of the UserForm, the accelerator keys for such controls continue to function. In this example, the user can press the Alt+L hot key (to select the Landscape mode option) even if that option isn't visible. To block access to nondisplayed controls, you can write code to disable the controls when they aren't displayed.
The example in this section is available on the companion CD-ROM. The file is named change userform size.xlsm.
Zooming and Scrolling a Sheet from a UserForm
The example in this section demonstrates how to use ScrollBar controls to allow sheet scrolling and zooming while a dialog box is displayed. Figure 14-7 shows how the example dialog box is set up. When the UserForm is displayed, the user can adjust the worksheet's zoom factor (from 10% to 400%) by using the ScrollBar at the top. The two ScrollBars in the bottom section of the dialog box allow the user to scroll the worksheet horizontally and vertically.
This example, named zoom and scroll sheet.xlsm, is available on the companion CD-ROM.
If you look at the code for this example, you'll see that it's remarkably simple. The controls are initialized in the UserForm_Initialize procedure, which follows:
Private Sub UserForm_Initialize()
LabelZoom.Caption = ActiveWindow.Zoom & “%”
‘ Zoom
With ScrollBarZoom
.Min = 10
.Max = 400
.SmallChange = 1
.LargeChange = 10
.Value = ActiveWindow.Zoom
End With
‘ Horizontally scrolling
With ScrollBarColumns
.Min = 1
.Max = ActiveSheet.UsedRange.Columns.Count
.Value = ActiveWindow.ScrollColumn
.LargeChange = 25
.SmallChange = 1
End With
‘ Vertically scrolling
With ScrollBarRows
.Min = 1
.Max = ActiveSheet.UsedRange.Rows.Count
.Value = ActiveWindow.ScrollRow
.LargeChange = 25
.SmallChange = 1
End With
End Sub
FIGURE 14-7: Here, ScrollBar controls allow zooming and scrolling of the worksheet.
This procedure sets various properties of the ScrollBar controls by using values based on the active window.
When the ScrollBarZoom control is used, the ScrollBarZoom_Change procedure (which follows) is executed. This procedure sets the ScrollBar control's Value to the ActiveWindow's Zoom property value. It also changes a label to display the current zoom factor.
Private Sub ScrollBarZoom_Change()
With ActiveWindow
.Zoom = ScrollBarZoom.Value
LabelZoom = .Zoom & “%”
End With
End Sub
Worksheet scrolling is accomplished by the two procedures that follow. These procedures set the ScrollRow or ScrollColumns property of the ActiveWindow object equal to the appropriate ScrollBar control value.
Private Sub ScrollBarColumns_Change()
ActiveWindow.ScrollColumn = ScrollBarColumns.Value
End Sub
Private Sub ScrollBarRows_Change()
ActiveWindow.ScrollRow = ScrollBarRows.Value
End Sub
Rather than use the Change event in the preceding procedures, you can use the Scroll event. The difference is that the event is triggered when the ScrollBars are dragged — resulting in smooth zooming and scrolling. To use the Scroll event, just make the Change part of the procedure name Scroll.
ListBox Techniques
The ListBox control is extremely versatile, but it can be a bit tricky to work with. This section contains of a number of examples that demonstrate common techniques that involve the ListBox control.
In most cases, the techniques described in this section also work with a ComboBox control.
Following are a few points to keep in mind when working with ListBox controls. Examples in the sections that follow demonstrate many of these points:
• You can retrieve the items in a ListBox from a range of cells (specified by the RowSource property), or you can add them by using VBA code (using the AddItem method).
• You can set up a ListBox to allow a single selection or a multiple selection. You use the MultiSelect property to specify the type of selection allowed.
• If a ListBox isn't set up for a multiple selection, you can link the value of the ListBox to a worksheet cell by using the ControlSource property.
• You can display a ListBox with no items selected (the ListIndex property will be –1). However, after an item is selected, the user can't deselect all items. The exception is if the MultiSelect property is True.
• A ListBox can contain multiple columns (controlled by the ColumnCount property) and even a descriptive header (controlled by the ColumnHeads property).
• The vertical height of a ListBox displayed in a UserForm window isn't always the same as the vertical height when the UserForm is actually displayed.
• You can display the items in a ListBox either as check boxes (if multiple selection is allowed) or as option buttons (if a single selection is allowed). The display type is controlled by the ListStyle property.
For complete details on the properties and methods for a ListBox control, consult the Help system.
Adding items to a ListBox control
Before displaying a UserForm that uses a ListBox control, you need to fill the ListBox with items. You can fill a ListBox at design time using items stored in a worksheet range, or at runtime using VBA to add the items to the ListBox.
The two examples in this section presume that
• You have a UserForm named UserForm1.
• This UserForm contains a ListBox control named ListBox1.
• The workbook contains a sheet named Sheet1, and range A1:A12 contains the items to be displayed in the ListBox.
Adding items to a ListBox at design time
To add items to a ListBox at design time, the ListBox items must be stored in a worksheet range. Use the RowSource property to specify the range that contains the ListBox items. Figure 14-8 shows the Properties window for a ListBox control. The RowSource property is set to Sheet1!A1:A12. When the UserForm is displayed, the ListBox will contain the 12 items in this range. The items appear in the ListBox at design time as soon as you specify the range for the RowSource property.
FIGURE 14-8: Setting the RowSource property at design time.
In most cases, you'll want to include the worksheet name when you specify the RowSource property; otherwise, the ListBox will use the specified range on the active worksheet. In some cases, you may need to fully qualify the range by including the workbook name. For example:
A better practice is to define a name for the range and use that name in your code. This habit will ensure that the proper range is used even if rows above the range are added or deleted.
Adding items to a ListBox at runtime
To add ListBox items at runtime, you have two choices:
• Set the RowSource property to a range address by using code.
• Write code that uses the AddItem method to add the ListBox items.
As you might expect, you can set the RowSource property via code rather than with the Properties window. For example, the following procedure sets the RowSource property for a ListBox before displaying the UserForm. In this case, the items consist of the cell entries in a range named Categories on the Budget worksheet.
UserForm1.ListBox1.RowSource = “Budget!Categories”
UserForm1.Show
If the ListBox items aren't contained in a worksheet range, you can write VBA code to fill the ListBox before the dialog box appears. The following procedure fills the ListBox with the names of the months by using the AddItem method.
Sub ShowUserForm2()
‘ Fill the list box
With UserForm1.ListBox1
.RowSource=””
.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
UserForm1.Show
End Sub
In the preceding code, notice that I set the RowSource property to an empty string. This setting is to avoid a potential error that occurs if the Properties window has a nonempty RowSource setting. If you try to add items to a ListBox that has a non-null RowSource setting, you'll get a “permission denied” error.
You can also use the AddItem method to retrieve ListBox items from a range. Here's an example that fills a ListBox with the contents of A1:A12 on Sheet1.
For Row = 1 To 12
UserForm1.ListBox1.AddItem Sheets(“Sheet1”).Cells(Row, 1)
Next Row
Using the List property is even simpler. The statement that follows has the same effect as the preceding For Next loop.
UserForm1.ListBox1.List = Application.Transpose(Sheets(“Sheet1”). _Range(“A1:A12”))
Note that I used the Transpose function because the List property expects a horizontal array and the range is in a column rather than a row.
You can also use the List property if your data is stored in a one-dimensional array. For example, assume that you have an array named MyList that contains 50 elements. The following statement will create a 50-item list in ListBox1:
UserForm1.ListBox1.List = MyList
The examples in this section are available on the companion CD-ROM. The file is named listbox fill.xlsm.
Adding only unique items to a ListBox
In some cases, you may need to fill a ListBox with unique (nonduplicated) items from a list. For example, assume that you have a worksheet that contains customer data. One of the columns might contain the state (see Figure 14-9). You'd like to fill a ListBox with the state names of your customers, but you don't want to include duplicate state names.
One technique involves using a Collection object. After creating a new Collection object, you can add items to the object with the following syntax:
object.Add item, key, before, after
The key argument, if used, must be a unique text string that specifies a separate key that you can use to access a member of the collection. The important word here is unique. If you attempt to add a non-unique key to a collection, an error occurs, and the item isn't added. You can take advantage of this situation and use it to create a collection that consists only of unique items.
The following procedure starts by declaring a new Collection object named NoDupes. It assumes that a range named Data contains a list of items, some of which may be duplicated.
FIGURE 14-9: A Collection object is used to fill a ListBox with the unique items from column B.
The code loops through the cells in the range and attempts to add the cell's value to the NoDupes collection. It also uses the cell's value (converted to a string) for the key argument. Using the On Error Resume Next statement causes VBA to ignore the error that occurs if the key isn't unique. When an error occurs, the item isn't added to the collection — which is just what you want. The procedure then transfers the items in the NoDupes collection to the ListBox. The UserForm also contains a label that displays the number of unique items.
Sub RemoveDuplicates1()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
On Error Resume Next
For Each Cell In Range(“State”)
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0
‘ Add the non-duplicated items to a ListBox
For Each Item In NoDupes
UserForm1.ListBox1.AddItem Item
Next Item
‘ Display the count
UserForm1.Label1.Caption = _
“Unique items: “ & NoDupes.Count
‘ Show the UserForm
UserForm1.Show
End Sub
This example, named listbox unique items1.xlsm, is available on the companion CD-ROM. A workbook named listbox unique items2.xlsm has a slightly more sophisticated version of this technique and displays the items sorted.
Determining the selected item in a ListBox
The examples in the preceding sections merely display a UserForm with a ListBox filled with various items. These procedures omit a key point: how to determine which item or items were selected by the user.
This discussion assumes a single-selection ListBox object — one whose MultiSelect property is set to 0.
To determine which item was selected, access the ListBox's Value property. The statement that follows, for example, displays the text of the selected item in ListBox1.
MsgBox ListBox1.Value
If no item is selected, this statement will generate an error.
If you need to know the position of the selected item in the list (rather than the content of that item), you can access the ListBox's ListIndex property. The following example uses a message box to display the item number of the selected ListBox item:
MsgBox “You selected item #” & ListBox1.ListIndex
If no item is selected, the ListIndex property will return –1.
The numbering of items in a ListBox begins with 0 — not 1. Therefore, the ListIndex of the first item is 0, and the ListIndex of the last item is equivalent to the value of the ListCount property less 1.
Determining multiple selections in a ListBox
A ListBox's MultiSelect property can be any of three values:
• 0 (fmMultiSelectSingle): Only one item can be selected. This setting is the default.
• 1 (fmMultiSelectMulti): Pressing the spacebar or clicking selects or deselects an item in the list.
• 2 (fmMultiSelectExtended): Shift-clicking extends the selection from the previously selected item to the current item. You can also use Shift and one of the arrow keys to extend the selected items.
If the ListBox allows multiple selections (that is, if its MultiSelect property is either 1 or 2), trying to access the ListIndex or Value property will result in an error. Instead, you need to use the Selected property, which returns an array whose first item has an index of 0. For example, the following statement displays True if the first item in the ListBox list is selected:
MsgBox ListBox1.Selected(0)
The companion CD-ROM contains a workbook that demonstrates how to identify the selected item(s) in a ListBox. It works for single-selection and multiple-selection ListBoxes. The file is named listbox selected items.xlsm.
The following code, from the example workbook on the CD-ROM, loops through each item in the ListBox. If the item was selected, it appends the item's text to a variable called Msg. Finally, the names of all the selected items are displayed in a message box.
Private Sub OKButton_Click()
Msg = “”
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then _
Msg = Msg & ListBox1.List(i) & vbCrLf
Next i
MsgBox ”You selected: ” & vbCrLf & Msg
Unload Me
End Sub
Figure 14-10 shows the result when multiple ListBox items are selected.
FIGURE 14-10: This message box displays a list of items selected in a ListBox.
Multiple lists in a single ListBox
This example demonstrates how to create a ListBox in which the contents change depending on the user's selection from a group of OptionButtons.
Figure 14-11 shows the UserForm. The ListBox gets its items from a worksheet range. The procedures that handle the Click event for the OptionButton controls simply set the ListBox's RowSource property to a different range. One of these procedures follows:
Private Sub obMonths_Click()
ListBox1.RowSource = “Sheet1!Months”
End Sub
FIGURE 14-11: The contents of this ListBox depend on the OptionButton selected.
Clicking the OptionButton named obMonths changes the RowSource property of the ListBox to use a range named Months on Sheet1.
This example, named listbox multiple lists.xlsm, is available on the companion CD-ROM.
ListBox item transfer
Some applications require a user to select several items from a list. It's often useful to create a new list of the selected items and display the new list in another ListBox. For an example of this situation, check out the Quick Access Toolbar tab of the Excel Options dialog box.
Figure 14-12 shows a dialog box with two ListBoxes. The Add button adds the item selected in the left ListBox to the right ListBox. The Remove button removes the selected item from the list on the right. A check box determines the behavior when a duplicate item is added to the list: Namely, if the Allow Duplicates check box isn't marked, a message box appears if the user attempts to add an item that's already on the list.
FIGURE 14-12: Building a list from another list.
The code for this example is relatively simple. Here's the procedure that is executed when the user clicks the Add button:
Private Sub AddButton_Click()
If ListBox1.ListIndex = -1 Then Exit Sub
If Not cbDuplicates Then
‘ See if item already exists
For i = 0 To ListBox2.ListCount - 1
If ListBox1.Value = ListBox2.List(i) Then
Beep
Exit Sub
End If
Next i
End If
ListBox2.AddItem ListBox1.Value
End Sub
The code for the Remove button is even simpler:
Private Sub RemoveButton_Click()
If ListBox2.ListIndex = -1 Then Exit Sub
ListBox2.RemoveItem ListBox2.ListIndex
End Sub
Notice that both of these routines check to make sure that an item is actually selected. If the ListBox's ListIndex property is –1, no items are selected, and the procedure ends.
This example has two additional procedures that control whether the Remove button is enabled or disabled. These events are triggered when the ListBox is entered (either via a keystroke or a mouse click). The net effect is that the Remove button is enabled only when the user is working in ListBox2.
Private Sub ListBox1_Enter()
RemoveButton.Enabled = False
End Sub
Private Sub ListBox2_Enter()
RemoveButton.Enabled = True
End Sub
This example, named listbox item transfer.xlsm, is available on the companion CD-ROM.
Moving items in a ListBox
Often, the order of items in a list is important. The example in this section demonstrates how to allow the user to move items up or down in a ListBox. The VBE uses this type of technique to let you control the tab order of the items in a UserForm. (Right-click a UserForm and choose Tab Order from the shortcut menu.)
Figure 14-13 shows a dialog box that contains a ListBox and two CommandButtons. Clicking the Move Up button moves the selected item up in the ListBox; clicking the Move Down button moves the selected item down.
This example, named listbox move items.xlsm, is available on the companion CD-ROM.
FIGURE 14-13: The buttons allow the user to move items up or down in the ListBox.
The event-handler procedures for the two CommandButtons follow:
Private Sub MoveUpButton_Click()
Dim NumItems As Integer, i As Integer, ItemNum As Integer
Dim TempItem As String, TempList()
If ListBox1.ListIndex <= 0 Then Exit Sub
NumItems = ListBox1.ListCount
Dim TempList()
ReDim TempList(0 To NumItems - 1)
‘ Fill array with list box items
For i = 0 To NumItems - 1
TempList(i) = ListBox1.List(i)
Next i
‘ Selected item
ItemNum = ListBox1.ListIndex
‘ Exchange items
TempItem = TempList(ItemNum)
TempList(ItemNum) = TempList(ItemNum - 1)
TempList(ItemNum - 1) = TempItem
ListBox1.List = TempList
‘ Change the list index
ListBox1.ListIndex = ItemNum - 1
End Sub
Private Sub MoveDownButton_Click()
Dim NumItems As Integer, i As Integer, ItemNum As Integer
Dim TempItem As String, TempList()
If ListBox1.ListIndex = ListBox1.ListCount - 1 Then Exit Sub
NumItems = ListBox1.ListCount
Dim TempList()
ReDim TempList(0 To NumItems - 1)
‘ Fill array with list box items
For i = 0 To NumItems - 1
TempList(i) = ListBox1.List(i)
Next i
‘ Selected item
ItemNum = ListBox1.ListIndex
‘ Exchange items
TempItem = TempList(ItemNum)
TempList(ItemNum) = TempList(ItemNum + 1)
TempList(ItemNum + 1) = TempItem
ListBox1.List = TempList
‘ Change the list index
ListBox1.ListIndex = ItemNum + 1
End Sub
Working with multicolumn ListBox controls
A normal ListBox has a single column for its contained items. You can, however, create a ListBox that displays multiple columns and (optionally) column headers. Figure 14-14 shows an example of a multicolumn ListBox that gets its data from a worksheet range.
This example, named listbox multicolumn1.xlsm, is available on the companion CD-ROM.
FIGURE 14-14: This ListBox displays a three-column list with column headers.
To set up a multicolumn ListBox that uses data stored in a worksheet range, follow these steps:
1. Make sure that the ListBox's ColumnCount property is set to the correct number of columns.
2. Specify the correct multicolumn range in the Excel worksheet as the ListBox's RowSource property.
3. If you want to display column headers, set the ColumnHeads property to True.
Do not include the column headings on the worksheet in the range setting for the RowSource property. VBA will instead automatically use the row directly above the first row of the RowSource range.
4. Adjust the column widths by assigning a series of values, specified in points (1⁄72 of one inch) and separated by semicolons, to the ColumnWidths property.
For example, for a three-column list box, the ColumnWidths property might be set to the following text string:
110 pt;40 pt;30 pt
5. Specify the appropriate column as the BoundColumn property.
The bound column specifies which column is referenced when an instruction polls the ListBox's Value property.
To fill a ListBox with multicolumn data without using a range, you first create a two-dimensional array and then assign the array to the ListBox's List property. The following statements demonstrate this using a 12-row x 2-column array named Data. The two-column ListBox shows the month names in column 1 and the number of the days in the month in column 2 (see Figure 14-15). Notice that the procedure sets the ColumnCount property to 2.
Private Sub UserForm_Initialize()
‘ Fill the list box
Dim Data(1 To 12, 1 To 2)
For i = 1 To 12
Data(i, 1) = Format(DateSerial(2010, i, 1), “mmmm”)
Next i
For i = 1 To 12
Data(i, 2) = Day(DateSerial(2010, i + 1, 1) - 1)
Next i
ListBox1.ColumnCount = 2
ListBox1.List = Data
End Sub
This example is available on the companion CD-ROM. The file is named listbox multicolumn2.xlsm.
There appears to be no way to specify column headers for the ColumnHeads property when the list source is a VBA array.
FIGURE 14-15: A two-column ListBox filled with data stored in an array.
Using a ListBox to select worksheet rows
The example in this section displays a ListBox that consists of the entire used range of the active worksheet (see Figure 14-16). The user can select multiple items in the ListBox. Clicking the All button selects all items, and clicking the None button deselects all items. Clicking OK selects those corresponding rows in the worksheet. You can, of course, select multiple noncontiguous rows directly in the worksheet by pressing Ctrl while you click the row borders. However, you might find that selecting rows is easier when using this method.
This example, named listbox select rows.xlsm, is available on the companion CD-ROM.
Selecting multiple items is possible because the ListBox's MultiSelect property is set to 1 - fmMultiSelectMulti. The check boxes on each item are displayed because the ListBox's ListStyle property is set to 1 - fmListStyleOption.
The UserForm's Initialize procedure follows. This procedure creates a Range object named rng that consists of the active sheet's used range. Additional code sets the ListBox's ColumnCount and RowSource properties and adjusts the ColumnWidths property so that the ListBox columns are proportional to the column widths in the worksheet.
Private Sub UserForm_Initialize()
Dim ColCnt As Integer
Dim rng As Range
Dim cw As String
Dim c As Integer
ColCnt = ActiveSheet.UsedRange.Columns.Count
Set rng = ActiveSheet.UsedRange
With ListBox1
.ColumnCount = ColCnt
.RowSource = rng.Address
cw = “”
For c = 1 To .ColumnCount
cw = cw & rng.Columns(c).Width & “;”
Next c
.ColumnWidths = cw
.ListIndex = 0
End With
End Sub
FIGURE 14-16: This ListBox makes selecting rows in a worksheet easy.
The All and None buttons (named SelectAllButton and SelectNoneButton, respectively) have simple event-handler procedures as follows:
Private Sub SelectAllButton_Click()
Dim r As Integer
For r = 0 To ListBox1.ListCount - 1
ListBox1.Selected(r) = True
Next r
End Sub
Private Sub SelectNoneButton_Click()
Dim r As Integer
For r = 0 To ListBox1.ListCount - 1
ListBox1.Selected(r) = False
Next r
End Sub
The OKButton_Click procedure follows. This procedure creates a Range object named RowRange that consists of the rows that correspond to the selected items in the ListBox. To determine whether a row was selected, the code examines the Selected property of the ListBox control. Notice that it uses the Union function to add ranges to the RowRange object.
Private Sub OKButton_Click()
Dim RowRange As Range
Dim RowCnt As Integer,r As Integer
RowCnt = 0
For r = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(r) Then
RowCnt = RowCnt + 1
If RowCnt = 1 Then
Set RowRange = ActiveSheet.UsedRange.Rows(r + 1)
Else
Set RowRange = _
Union(RowRange, ActiveSheet.UsedRange.Rows(r + 1))
End If
End If
Next r
If Not RowRange Is Nothing Then RowRange.Select
Unload Me
End Sub
This example is available on the companion CD-ROM. The file is named listbox select rows.xlsm.
Using a ListBox to activate a sheet
The example in this section is just as useful as it is instructive. This example uses a multicolumn ListBox to display a list of sheets within the active workbook. The columns represent
• The sheet's name
• The type of sheet (worksheet, chart, or Excel 5/95 dialog sheet)
• The number of nonempty cells in the sheet
• Whether the sheet is visible
Figure 14-17 shows an example of the dialog box.
The code in the UserForm_Initialize procedure (which follows) creates a two-dimensional array and collects the information by looping through the sheets in the active workbook. It then transfers this array to the ListBox.
FIGURE 14-17: This dialog box lets the user activate a sheet.
Public OriginalSheet As Object
Private Sub UserForm_Initialize()
Dim SheetData() As String, Sht As Object
Dim ShtCnt As Integer, ShtNum As Integer, ListPos As Integer
Set OriginalSheet = ActiveSheet
ShtCnt = ActiveWorkbook.Sheets.Count
ReDim SheetData(1 To ShtCnt, 1 To 4)
ShtNum = 1
For Each Sht In ActiveWorkbook.Sheets
If Sht.Name = ActiveSheet.Name Then _
ListPos = ShtNum - 1
SheetData(ShtNum, 1) = Sht.Name
Select Case TypeName(Sht)
Case “Worksheet”
SheetData(ShtNum, 2) = “Sheet”
SheetData(ShtNum, 3) = _
Application.CountA(Sht.Cells)
Case “Chart”
SheetData(ShtNum, 2) = “Chart”
SheetData(ShtNum, 3) = “N/A”
Case “DialogSheet”
SheetData(ShtNum, 2) = “Dialog”
SheetData(ShtNum, 3) = ”N/A”
End Select
If Sht.Visible Then
SheetData(ShtNum, 4) = ”True”
Else
SheetData(ShtNum, 4) = ”False”
End If
ShtNum = ShtNum + 1
Next Sht
With ListBox1
.ColumnWidths = ”100 pt;30 pt;40 pt;50 pt”
.List = SheetData
.ListIndex = ListPos
End With
End Sub
The ListBox1_Click procedure follows:
Private Sub ListBox1_Click()
If cbPreview Then _
Sheets(ListBox1.Value).Activate
End Sub
The value of the CheckBox control (named cbPreview) determines whether the selected sheet is previewed when the user clicks an item in the ListBox.
Clicking the OK button (named OKButton) executes the OKButton_Click procedure, which follows:
Private Sub OKButton_Click()
Dim UserSheet As Object
Set UserSheet = Sheets(ListBox1.Value)
If UserSheet.Visible Then
UserSheet.Activate
Else
If MsgBox(“Unhide sheet?”, _
vbQuestion + vbYesNoCancel) = vbYes Then
UserSheet.Visible = True
UserSheet.Activate
Else
OriginalSheet.Activate
End If
End If
Unload Me
End Sub
The OKButton_Click procedure creates an object variable that represents the selected sheet. If the sheet is visible, it's activated. If it's not visible, the user is presented with a message box asking whether it should be unhidden. If the user responds in the affirmative, the sheet is unhidden and activated. Otherwise, the original sheet (stored in a public object variable named OriginalSheet) is activated.
Double-clicking an item in the ListBox has the same result as clicking the OK button. The ListBox1_DblClick procedure, which follows, simply calls the OKButton_Click procedure.
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Call OKButton_Click
End Sub
This example is available on the companion CD-ROM. The file is named listbox activate sheet.xlsm.
Using the MultiPage Control in a UserForm
The MultiPage control is useful for UserForms that must display many controls. The MultiPage control lets you group the choices and place each group on a separate tab.
Figure 14-18 shows an example of a UserForm that contains a MultiPage control. In this case, the control has three pages, each with its own tab.
This example is available on the companion CD-ROM. The file is named multipage control demo.xlsm.
FIGURE 14-18: MultiPage groups your controls on pages, making them accessible from a tab.
The Toolbox also contains a control named TabStrip, which resembles a MultiPage control. However, unlike the MultiPage control, the TabStrip control isn't a container for other objects. The MultiPage control is much more versatile, and I've never had a need to actually use the TabStrip control.
Using a MultiPage control can be a bit tricky. The following are some things to keep in mind when using this control:
• The tab (or page) that's displayed up front is determined by the control's Value property. A value of 0 displays the first tab, a value of 1 displays the second tab, and so on.
• By default, a MultiPage control has two pages. To add a new page in the VBE, right-click a tab and select New Page from the shortcut menu.
• When you're working with a MultiPage control, just click a tab to set the properties for that particular page. The Properties window will display the properties that you can adjust.
• You may find it difficult to select the actual MultiPage control because clicking the control selects a page within the control. To select the control itself, click its border. Or, you can use the Tab key to cycle among all the controls. Yet another option is to select the MultiPage control from the drop-down list in the Properties window.
• If your MultiPage control has lots of tabs, you can set its MultiRow property to True to display the tabs in more than one row.
• If you prefer, you can display buttons instead of tabs. Just change the Style property to 1. If the Style property value is 2, the MultiPage control won't display tabs or buttons.
• The TabOrientation property determines the location of the tabs on the MultiPage control.
• For each page, you can set a transition effect by changing the TransitionEffect property. For example, clicking a tab can cause the new page to push the former page out of the way. Use the TransitionPeriod property to set the speed of the transition effect.
Using an External Control
The example in this section uses the Windows Media Player control. Although this control isn't an Excel control (it's installed with Windows), it works fine in a UserForm.
To make this control available, add a UserForm to a workbook and follow these steps:
1. Activate the VBE.
2. Right-click the Toolbox and choose Additional Controls.
Choose View⇒Toolbox if the Toolbox isn't visible.
3. In the Additional Controls dialog box, scroll down and place a check mark next to Windows Media Player.
4. Click OK.
Your Toolbox will display a new control.
Figure 14-19 shows the Windows Media Player control in a UserForm, along with the Property window. The URL property represents the media item being played (music or video). If the item is on your hard drive, the URL property will contain the full path along with the filename.
FIGURE 14-19: The Date and Time Picker control in a UserForm.
Figure 14-20 shows this control being used. I reduced the height of the Windows Media Player control to hide the video display. I added a ListBox, which is filled with MP3 audio filenames. Clicking the Play button plays the selected file. Clicking the Close button stops the sound and closes the UserForm. This UserForm is displayed modeless, so the user can continue working when the dialog box is displayed.
This example is available on the companion CD-ROM. The file is named mediaplayer.xlsm, and it's in a separate directory that includes some public domain MP3 sound effect files.
FIGURE 14-20: The Windows Media Player control.
This example was incredibly easy to create. The UserForm_Initialize procedure adds the MP3 filenames to the ListBox. To keep things simple, it reads the files that are in the same directory as the workbook. A more versatile approach is to let the user select a directory.
Private Sub UserForm_Initialize()
Dim FileName As String
‘ Fill listbox with MP3 files
FileName = Dir(ThisWorkbook.Path & “*.mp3”, vbNormal)
Do While Len(FileName) > 0
ListBox1.AddItem FileName
FileName = Dir()
Loop
ListBox1.ListIndex = 0
End Sub
See Chapter 27 for more information about using the Dir command.
The PlayButton_Click event-handler code consists of a single statement, which assigns the selected filename to the URL property of the WindowsMediaPlayer1 object.
Private Sub PlayButton_Click()
‘ URL property loads track, and starts player
WindowsMediaPlayer1.URL = _
ThisWorkbook.Path & “” & ListBox1.List(ListBox1.ListIndex)
End Sub
You can probably think of lots of enhancements for this simple application.
Animating a Label
The final example in this chapter demonstrates how to animate a Label control. The UserForm shown in Figure 14-21 is an interactive random number generator.
Two TextBox controls hold the lower and upper values for the random number. A Label control initially displays four question marks, but the text is animated to show random numbers when the user clicks the Start button. The Start button changes to a Stop button, and clicking it again stops the animation and displays the random number. Figure 14-22 shows the dialog box displaying a random number between 1 and 10,000.
FIGURE 14-21: Generating a random number.
FIGURE 14-22: A random number has been chosen.
The code that's attached to the button is as follows:
Dim Stopped As Boolean
Private Sub StartStopButton_Click()
Dim Low As Double, Hi As Double
If StartStopButton.Caption = “Start” Then
‘ validate low and hi values
If Not IsNumeric(TextBox1.Text) Then
MsgBox “Non-numeric starting value.”, vbInformation
With TextBox1
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
Exit Sub
End If
If Not IsNumeric(TextBox2.Text) Then
MsgBox “Non-numeric ending value.”, vbInformation
With TextBox2
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
Exit Sub
End If
‘ Make sure they aren't in the wrong order
Low = Application.Min(Val(TextBox1.Text), Val(TextBox2.Text))
Hi = Application.Max(Val(TextBox1.Text), Val(TextBox2.Text))
‘ Adjust font size, if necessary
Select Case Application.Max(Len(TextBox1.Text), Len(TextBox2.Text))
Case Is < 5: Label1.Font.Size = 72
Case 5: Label1.Font.Size = 60
Case 6: Label1.Font.Size = 48
Case Else: Label1.Font.Size = 36
End Select
StartStopButton.Caption = “Stop”
Stopped = False
Randomize
Do Until Stopped
Label1.Caption = Int((Hi - Low + 1) * Rnd + Low)
DoEvents ‘ Causes the animation
Loop
Else
Stopped = True
StartStopButton.Caption = “Start”
End If
End Sub
Because the button serves two purposes (starting and stopping), the procedure uses a public variable, Stopped, to keep track of the state. The first part of the procedure consists of two If-Then structures to validate the contents of the TextBox controls. Two more statements ensure that the low value is in fact less than the high value. The next section adjusts the Label control's font size, based on the maximum value. The Do Until loop is responsible for generating and displaying the random numbers. Notice the DoEvents statement. This statement causes Excel to “yield” to the operating system. Without the statement, the Label control wouldn't display each random number as it's generated. In other words, the DoEvents statement is what makes the animation possible.
The UserForm also contains a CommandButton that serves as a Cancel button. This control is positioned off the UserForm so that it's not visible. This CommandButton has its Cancel property set to True, so pressing Esc is equivalent to clicking the button. Its click event-handler procedure simply sets the Stopped variable to True and unloads the UserForm:
Private Sub CancelButton_Click()
Stopped = True
Unload Me
End Sub
This example, named random number generator.xlsm, is available on the companion CD-ROM.