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:

Private Sub CommandButton1_Click()
    Me.Hide
    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.

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 Me.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 Me.ListBox1.ListIndex
        Case -1
            MsgBox"Select a macro from the list."
            Exit Sub
        Case 0: Macro1
        Case 1: Macro2
        Case 2: Macro3
        Case 3: Macro4
        Case 4: Macro5
        Case 5: 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.

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 14.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.

    UserForm1.ListBox1.RowSource ="Budget!Categories"
    UserForm1.Show

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

For Row = 1 To 12
  UserForm1.ListBox1.AddItem Sheets("Sheet1").Cells(Row, 1)
Next Row

UserForm1.ListBox1.List = _
   Application.Transpose(Sheets("Sheet1").Range("A1:A12"))

UserForm1.ListBox1.List = MyList

UserForm1.ListBox1.List = Array("January","February", _
    "March","April","May","June","July","August", _
    "September","October","November","December")
UserForm1.ListBox1.List = Split("Mon Tue Wed Thu Fri Sat Sun")

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.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.

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 Collection object named NoDupes. It assumes that a range named States 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 Collection
    Dim Item as Variant
    
    Set NoDupes = New Collection
    
    On Error Resume Next
    For Each Cell In Range("State").Cells
        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

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.

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 Me.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 #" & Me.ListBox1.ListIndex

If no item is selected, the ListIndex property will return -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)

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 cmdOK_Click()
    Dim Msg As String
    Dim i As Long
    
    If Me.ListBox1.ListIndex = -1 Then
        Msg ="Nothing"
    Else
        For i = 0 To Me.ListBox1.ListCount - 1
            If ListBox1.Selected(i) Then _
              Msg = Msg & Me.ListBox1.List(i) & vbNewLine
        Next i
    End If
    MsgBox"You selected:" & vbNewLine & Msg
    Unload Me
End Sub

Figure 14.9 shows the result when multiple ListBox items are selected.

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 optMonths_Click()
    Me.ListBox1.RowSource ="Sheet1!Months"
End Sub

Figure 14.10 shows the UserForm.

Clicking the OptionButton named optMonths changes the RowSource property of the ListBox to use a range named Months on Sheet1.

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.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, the item is removed from the From list so it can’t be added again.

The code for this example is simple. Here’s the procedure that is executed when the user clicks the Add button:

Private Sub cmdAdd_Click()
    'Add the value
    Me.lbxTo.AddItem Me.lbxFrom.Value
    If Not Me.chkDuplicates.Value Then
        'If duplicates aren't allowed, remove the value
        Me.lbxFrom.RemoveItem Me.lbxFrom.ListIndex
    End If
    EnableButtons
End Sub

The code for the Remove button is very similar:

Private Sub cmdRemove_Click()
    If Not Me.chkDuplicates.Value Then
        Me.lbxFrom.AddItem Me.lbxTo.Value
    End If
    Me.lbxTo.RemoveItem Me.lbxTo.ListIndex
    EnableButtons
End Sub

Note that neither routine checks to make sure that an item is actually selected. Instead, both button’s Enabled property is set to False at design time. Another procedure, EnableButtons, is called to enable the buttons only when it’s appropriate.

In addition to calling EnableButtons from cmdAdd_Click and cmdRemove_Click, both ListBox’s Change events also call it. The ListBox’s Change event procedures and the EnableButtons procedure are shown below.

Private Sub lbxFrom_Change()
    EnableButtons
End Sub

Private Sub lbxTo_Change()
    EnableButtons
End Sub

Private Sub EnableButtons()
    Me.cmdAdd.Enabled = Me.lbxFrom.ListIndex > -1
    Me.cmdRemove.Enabled = Me.lbxTo.ListIndex > -1
End Sub

The ListIndex property is compared to -1, which returns True or False. That value is assigned to the Enabled property to only allow the user to click a button when an item is selected.

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.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.

The event-handler procedures for the two CommandButtons follow:

Private Sub cmdUp_Click()
    Dim lSelected As Long
    Dim sSelected As String
    
'   Store the currently selected item
    lSelected = Me.lbxItems.ListIndex
    sSelected = Me.lbxItems.Value

'   Remove the selected item
    Me.lbxItems.RemoveItem lSelected
'   Add back the item one above
    Me.lbxItems.AddItem sSelected, lSelected - 1
'   Reselect the moved item
    Me.lbxItems.ListIndex = lSelected - 1
End Sub

Private Sub cmdDown_Click()
    Dim lSelected As Long
    Dim sSelected As String
    
'   Store the currently selected item
    lSelected = Me.lbxItems.ListIndex
    sSelected = Me.lbxItems.Value

'   Remove the selected item
    Me.lbxItems.RemoveItem lSelected
'   Add back the item one below
    Me.lbxItems.AddItem sSelected, lSelected + 1
'   Reselect the moved item
    Me.lbxItems.ListIndex = lSelected + 1
End Sub

The up and down buttons are disabled by default (their Enabled property is set to False at design time). The Click event of the ListBox is used to enable the buttons only when it’s appropriate for them to be clicked. The cmdDown button is only enabled when something is selected (the ListIndex property is 0 or greater) and the item selected is not the last item. The cmdUp control is similarly enabled except that it tests that the item selected isn’t the first item. The event procedure is shown below:

Private Sub lbxItems_Click()
    Me.cmdDown.Enabled = Me.lbxItems.ListIndex > -1 _
        And Me.lbxItems.ListIndex < Me.lbxItems.ListCount - 1
    Me.cmdUp.Enabled = Me.lbxItems.ListIndex > -1 _
        And Me.lbxItems.ListIndex > 0
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

The ListBox examples so far have only had a single column for their items. You can, however, create a ListBox that displays multiple columns and (optionally) column headers. Figure 14.13 shows an example of a multicolumn ListBox that gets its data from a worksheet range.

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 14-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 14.14). Note that the procedure sets the ColumnCount property to 2.

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim Data(1 To 12, 1 To 2) As String
    Dim ThisYear As Long
    ThisYear = Year(Now)
'   Fill the list box
    For i = 1 To 12
        Data(i, 1) = Format(DateSerial(ThisYear, i, 1),"mmmm")
        Data(i, 2) = Day(DateSerial(ThisYear, i + 1, 0))
    Next i
    Me.ListBox1.ColumnCount = 2
    Me.ListBox1.List = Data
End Sub

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.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..

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 Long
    Dim rng As Range
    Dim ColWidths As String
    Dim i As Long
    
    ColCnt = ActiveSheet.UsedRange.Columns.Count
    Set rng = ActiveSheet.UsedRange
    With Me.lbxRange
        .ColumnCount = ColCnt
        .RowSource = _
            rng.Offset(1).Resize(rng.Rows.Count - 1).Address
        For i = 1 To .ColumnCount
            ColWidths = ColWidths & rng.Columns(i).Width &";"
        Next i
        .ColumnWidths = ColWidths
        .ListIndex = 0
    End With
End Sub

The All and None buttons (named cmdAll and cmdNone, respectively) have simple event-handler procedures:

Private Sub cmdAll_Click()
    Dim i As Long
    For i = 0 To Me.lbxRange.ListCount - 1
        Me.lbxRange.Selected(i) = True
    Next i
End Sub

Private Sub cmdNone_Click()
    Dim i As Long
    For i = 0 To Me.lbxRange.ListCount - 1
        Me.lbxRange.Selected(i) = False
    Next i
End Sub

The cmdOK_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 cmdOK_Click()
    Dim RowRange As Range
    Dim i As Long
    
    For i = 0 To Me.lbxRange.ListCount - 1
      If Me.lbxRange.Selected(i) Then
      If RowRange Is Nothing Then
      Set RowRange = ActiveSheet.UsedRange.Rows(i + 2)
      Else
          Set RowRange = Union(RowRange, ActiveSheet.UsedRange.Rows(i + 2))
      End If
      End If
    Next i
    If Not RowRange Is Nothing Then RowRange.Select
    Unload Me
End Sub

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 14.16 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.

Public OriginalSheet As Object

Private Sub UserForm_Initialize()
    Dim SheetData() As String, Sht As Object
    Dim ShtCnt As Long, ShtNum As Long, ListPos As Long

    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 Me.lbxSheets
        .ColumnWidths ="100 pt;30 pt;40 pt;50 pt"
        .List = SheetData
        .ListIndex = ListPos
    End With
End Sub

The lbxSheets_Click procedure follows:

Private Sub lbxSheets_Click()
    If chkPreview.Value Then Sheets(Me.lbxSheets.Value).Activate
End Sub

The value of the CheckBox control (named chkPreview) determines whether the selected sheet is previewed when the user clicks an item in the ListBox.

Clicking the OK button (named cmdOK) executes the cmdOK_Click procedure, which follows:

Private Sub cmdOK_Click()
    Dim UserSheet As Object
    Set UserSheet = Sheets(Me.lbxSheets.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 cmdOK_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 lbxSheets_DblClick procedure, which follows, simply calls the cmdOK_Click procedure.

Private Sub lbxSheets_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    cmdOK_Click
End Sub

Filtering a ListBox from a TextBox

If your ListBox has a large number of items, you can provide a way to filter the ListBox so that you don’t have to scroll through so many entries. Figure 14.17 shows a ListBox whose entries have been filtered by a TextBox.

The Userform uses a procedure named FillContacts, shown below, to add items to the ListBox. FillContacts accepts an optional argument that’s used to filter the contacts. If you don’t supply the sFilter argument, all 1,000 contacts are shown, otherwise only those contacts that match the filter are shown.

Private Sub FillContacts(Optional sFilter As String ="*")
    Dim i As Long, j As Long
    
    'Clear any existing entries in the ListBox
    Me.lbxContacts.Clear
    'Loop through all the rows and columns of the contact list
    For i = LBound(maContacts, 1) To UBound(maContacts, 1)
        For j = 1 To 4
            'Compare the contact to the filter
            If UCase(maContacts(i, j)) Like UCase("*" & sFilter &"*") Then
                'Add it to the ListBox
                With Me.lbxContacts
                    .AddItem maContacts(i, 1)
                    .List(.ListCount - 1, 1) = maContacts(i, 2)
                    .List(.ListCount - 1, 2) = maContacts(i, 3)
                    .List(.ListCount - 1, 3) = maContacts(i, 4)
                End With
                'If any column matched, skip the rest of the columns
                'and move to the next contact
                Exit For
            End If
        Next j
    Next i
    'Select the first contact
    If Me.lbxContacts.ListCount > 0 Then Me.lbxContacts.ListIndex = 0
End Sub

First, FillContacts clears any existing entries out of the ListBox. Next, the procedure loops through all of the rows and the four columns of an array and compares each value to sFilter. I use the Like operator and surround sFilter with asterisks so you can type any part of the value and still get a match. To make the filter case-insensitive, I convert both values to upper case using the UCase function. If any of the values (first name, last name, email, or department) matches the filter, that contact is added to the ListBox.

The maContacts array that FillContacts uses is created in the Userform_Initialize event. It fills the array using a table on Sheet1 called tblContacts. Then it calls FillContacts with no filter argument so that all contacts are shown initially. The code for the Initialize event follows:

Private maContacts As Variant

Private Sub UserForm_Initialize()
    maContacts = Sheet1.ListObjects("tblContacts").DataBodyRange.Value
    FillContacts
End Sub

Finally, the Change event for the TextBox also calls FillContacts. But instead of omitting the filter, this event supplies whatever text is currently in the TextBox. The Change event is one simple line of code:

Private Sub tbxSearch_Change()
    FillContacts Me.tbxSearch.Text
End Sub

This is a good example of using a non-event procedure in the Userform code module to do the work. Instead of duplicating code in the Userform_Initialize event and the tbxSearch_Change event, the two events simply call FillContacts.