Chapter 12: 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 13 contains additional examples of more advanced UserForm techniques.
Using CommandButtons in a UserForm
Figure 12-1 shows an example of a UserForm that uses CommandButton controls as a simple menu.
Figure 12-1: This dialog box uses CommandButtons as a 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:
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 12-2 shows another example that uses a ListBox as a menu.
Figure 12-2: This dialog box uses a ListBox as a menu.
This style is easier to maintain because you can easily add new menu items without adjusting the size of the UserForm. 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:
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 book's website in the userform menus.xlsm file.
Chapter 13 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, the control is mouse-centric. 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 to make a selection.
Figure 12-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 nonempty) cells in the selected range. The operation that's performed corresponds to the selected OptionButton.
Figure 12-3: The RefEdit control shown here allows the user to select a range.
This example is available on the book's website in a file named range selection demo.xlsm.
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 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 isolate 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 10, 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 introductory information when the application is opened. This display is commonly known as a splash screen.
You can create a splash screen for your Excel application with a UserForm. This example is essentially a UserForm that is displayed automatically when the workbook is opened, and then dismisses itself after five seconds.
This book's website contains a workbook that demonstrates this procedure in a file named splash screen.xlsm.
Follow these instructions to create a splash screen for your project:
1. Create your workbook.
2. Activate 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 12-4 shows an example.
Figure 12-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 a large 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), 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 books website 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 dismisses the UserForm. 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. This example displays a list of worksheets in the active workbook and lets the user select which sheets to print.
Refer to Chapter 13 for an example that allows the user to change the UserForm's size by dragging the lower-right corner.
Figure 12-5 shows the two states of the dialog box: as it is first displayed and after the user clicks the Options button. Note that the button's caption changes, depending on the size of the UserForm.
Figure 12-5: A dialog box before and after displaying 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 the UserForm 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
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 landscape mode) 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 book's website in the file 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 12-6 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.
Figure 12-6: Here, ScrollBar controls allow zooming and scrolling of the worksheet.
This example, named zoom and scroll sheet.xlsm, is available on the book's website.
The code for this example is 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
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
If you use the Scroll event rather than use the Change event in the preceding procedures, the event will be 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 versatile, but it can be 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 work also 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 at design time isn't always the same as the vertical height when the UserForm is displayed.
• You can display the items in a ListBox either as check boxes (if multiple selections are 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.
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 12-7 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 12-7: 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 workbook-level 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 through 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 in 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, note that I set the RowSource property to an empty string. This setting avoids 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 retrieve items from a range, and use the AddItem method to add them to the ListBox. 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 use the List property also 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 book's website in the file 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 12-8). You'd like to fill a ListBox with the state names of your customers, but you don't want to include duplicate state names.
Figure 12-8: A Collection object is used to fill a ListBox with the unique items from column B.
One fast and efficient 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 nonunique 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.
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 book's website. 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 minus 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): Press Ctrl and click to select multiple items. 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)
This book's website 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 website, loops through each item in the ListBox. If the item was selected, the item's text is appended to a variable called Msg. Finally, the names of all 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 12-9 shows the result when multiple ListBox items are selected.
Figure 12-9: 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.
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 12-10 shows the UserForm.
Figure 12-10: 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 book's website.
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 12-11 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, nothing happens if the user attempts to add an item that's already on the list.
Figure 12-11: Building a list from another list.
The code for this example is 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 Exit Sub
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
ListBox2.RemoveItem ListBox2.ListIndex
End If
If ListBox2.ListIndex = -1 Then RemoveButton.Enabled = False
End Sub
Note that both 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. When the Remove button is clicked, the code checks to see whether any items remain. If there are no items, the Remove button is disabled.
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 (by 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 book's website.
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. 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 12-12 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.
Figure 12-12: The buttons allow the user to move items up or down in the ListBox.
This example, named listbox move items.xlsm, is available on the book's website.
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
I noticed that, for some reason, rapid clicking of the Move Up or the Move Down button didn't register as multiple clicks. To fix this problem, I added two more procedures that respond to the Double Click event for each button. These procedures simply call the appropriate Click event procedure listed previously.
Working with multicolumn ListBox controls
A normal ListBox has a single column for its items. You can, however, create a ListBox that displays multiple columns and (optionally) column headers. Figure 12-13 shows an example of a multicolumn ListBox that gets its data from a worksheet range.
Figure 12-13: This ListBox displays a three-column list with column headers.
This example, named listbox multicolumn1.xlsm, is available on the book's website.
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 1 inch) and separated by semicolons, to the ColumnWidths property. This will almost always require some trial and error.
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-by-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 12-14). Note 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(2012, i, 1), “mmmm”)
Next i
For i = 1 To 12
Data(i, 2) = Day(DateSerial(2012, i + 1, 1) - 1)
Next i
ListBox1.ColumnCount = 2
ListBox1.List = Data
End Sub
Figure 12-14: A two-column ListBox filled with data stored in an array.
This example is available on the book's website in the file 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.
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 12-15). 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 might find that selecting multiple noncontiguous rows is easier when using this method rather than by pressing Ctrl while you click the row borders..
Figure 12-15: This ListBox makes selecting rows in a worksheet easy.
This example, named listbox select rows.xlsm, is available on the book's website.
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
The All and None buttons (named SelectAllButton and SelectNoneButton, respectively) have simple event-handler procedures:
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. Note 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 book's website in the file 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 in the active workbook. The columns represent
• The sheet's name
• The type of sheet (worksheet, chart sheet, or Excel 5/95 dialog sheet)
• The number of nonempty cells in the sheet
• Whether the sheet is visible
Figure 12-16 shows an example of the dialog box.
Figure 12-16: This dialog box lets the user activate a sheet.
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.
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 book's website in the file named listbox activate sheet.xlsm.
Using the MultiPage Control in a UserForm
The MultiPage control is useful for UserForms that must display many controls because it enables you to group choices and place each group on a separate tab.
Figure 12-17 shows an example of a UserForm that contains a MultiPage control. In this case, the control has three pages, each with its own tab.
Figure 12-17: MultiPage groups your controls on pages, making them accessible from a tab.
This example is available on the book's website in the file named multipage control demo.xlsm.
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 more versatile, and I've never had a need to use the TabStrip control.
Using a MultiPage control can be 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 VBE, right-click a tab and choose 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 choose 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.
Using an External Control
The example in this section uses the Windows Media Player ActiveX 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 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 12-18 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 12-18: The Windows Media Player control in a UserForm.
Figure 12-19 shows this control being used. The video is showing a visualization that changes in time to the audio. 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.
Figure 12-19: The Windows Media Player control.
This example is available on the book's website in the mediaplayer.xlsm file, which is in a separate directory that includes public domain MP3 sound files.
This example was 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 25 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. Also note that this control responds to many events.
Animating a Label
The final example in this chapter demonstrates how to animate a Label control. The UserForm shown in Figure 12-20 is an interactive random number generator.
Figure 12-20: Generating a random number.
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 12-21 shows the dialog box displaying a random number between -1,000 and 1000.
Figure 12-21: 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.
Note 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 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 book's website.