More User Form Examples

I could probably fill an entire book with interesting and useful tips for working with custom dialog boxes. Unfortunately, this book has a limited number of pages, so I wrap it up with a few more examples.

A ListBox example

ListBoxes are useful controls, but working with them can be a bit tricky. Before displaying a dialog box that uses a ListBox, fill the ListBox with items. Then when the dialog box is closed, you need to determine which item(s) the user selected.

When dealing with list boxes, you need to know about the following properties and methods:

il AddItem: You use this method to add an item to a ListBox.

l ListCount: This property returns the number of items in the ListBox.

i ListIndex: This property returns the index number of the selected item or sets the item that's selected (single selections only). The first item has a ListIndex of 0 (not 1).

i MultiSelect: This property determines whether the user can select more than one item from the ListBox.

i RemoveAllItems: Use this method to remove all items from a ListBox.

i Selected: This property returns an array indicating selected items (applicable only when multiple selections are allowed).

i Value: This property returns the selected item in a ListBox.

Most of the methods and properties that work with ListBoxes also work with ComboBoxes. Thus, after you have figured out how to handle ListBoxes, you can transfer that knowledge to your work with ComboBoxes.

Fitting a list box

For best results, start with an empty workbook. The example in this section assumes the following:

l You've added a UserForm.

l The UserForm contains a ListBox control named ListBox1.

i The UserForm has a CommandButton named OKButton.

l The UserForm has a CommandButton named CancelButton, which has the following event-handler procedure:

Private Sub CancelButton_Click()

Unload UserForm1 End Sub

The following procedure is stored in the Initialize procedure for the UserForm:

1. Select your UserForm and press F7 to find this predefined procedure.

The VBE displays the Code window for your form and stands ready for you to input the code for the Click event. (The procedure is UserForm_Click.)

2. Using the Procedure drop-down list at the top of the Code window, choose Initialize.

3. Add the initialization code for the form:

Sub

UserForm_Initialize()

Fill the list

box

With ListBoxl

.Addltem

"January"

.Addltem

"February"

.Addltem

"March"

.Addltem

"April"

.Addltem

"May"

.Addltem

"June"

.Addltem

"July"

.Addltem

"August"

.Addltem

"September"

.Addltem

"October"

.Addltem

"November"

.Addltem

"December"

End With

Select the first list item

ListBoxl.Listlndex = 0

End

Sub

This initialization routine runs automatically whenever your UserForm is loaded. Thus, when you use the Show method for the UserForm, the code is automatically run and your list is populated with 12 items, each added via the Addltem method.

You can now delete the empty "Userform_Click" procedure, as it is not needed in this example.

4. Create a VBA module with a small Sub procedure to simply display the dialog box:

Sub ShowList()

UserForml.Show End Sub

It is not mandatory to use the Initialize event procedure to populate your lists. You could do so in a regular VBA procedure. Using an Initialize event procedure just seems like a natural place to take care of such a mundane (though important) step. Note that the Initialize event runs when the UserForm is created in memory (you know, that blueprint stuff I was talking about earlier in this chapter). If you hide the UserForm by using UserForml.Hide and later on show the form again (UserForml.Show), the Initialize event does not fire again.

Determining the selected item

The preceding code merely displays a dialog box with a ListBox filled with month names. What's missing is a procedure to determine which item in the ListBox is selected.

_Chapter 18: UserForm Techniques and Tricks 285

Add the following to the OKButton_Click procedure:

Private Sub OKButton_Click() Dim Msg As String Msg = "You selected item # " Msg = Msg & ListBoxl.Listlndex Msg = Msg & vbNewLine Msg = Msg & ListBoxl.Value MsgBox Msg Unload UserForml End Sub

This procedure displays a message box with the selected item number and the selected item. Figure 18-4 shows how this looks.

Figure 18-4:

Determining which item in a ListBox is selected.

Figure 18-4:

Determining which item in a ListBox is selected.

The first item in a ListBox has a ListIndex of 0, not 1 (as you may expect). This is always the case, even if you use an Option Base 1 statement to change the default lower bound for arrays.

This example is available at this book's Web site.

Determining multiple selections

If your ListBox is set up so the user can select more than one item, you find that the ListIndex property returns only the last item selected. To determine all selected items, you need to use the Selected property, which contains an array.

To allow multiple selections in a ListBox, set the MultiSelect property to either 1 or 2. You can do so at design time by using the Properties window or at run time by using a VBA statement such as this:

UserForml.ListBoxl.MultiSelect = 1

The MultiSelect property has three possible settings. The meaning of each is shown in Table 18-1.

Table 18-1 Settings for the MultiSelect Property

Value

VBA Constant

Meaning

0

fmMultiSelectSingle

Only a single item can be selected.

1

fmMultiSelectMulti

Clicking an item or pressing the space bar selects or deselects an item in the list.

2 fmMultiSelectExtended Items are added to or removed from the selection set in the traditional manner: holding down the Shift or Ctrl key as you click items.

The following procedure displays a message box that lists all selected items in a ListBox. Figure 18-5 shows an example.

Figure 18-5:

Determining the selected items in a

ListBox allows multiple selections.

The following procedure displays a message box that lists all selected items in a ListBox. Figure 18-5 shows an example.

Figure 18-5:

Determining the selected items in a

ListBox allows multiple selections.

Private Sub OKButton_Click() Dim Msg As String Dim i As Integer

Msg = "You selected" & vbNewLine For i = 0 To ListBoxl.ListCount - 1 If ListBoxl.Selected(i) Then

Msg = Msg & ListBoxl.List(i) & vbNewLine End If Next i MsgBox Msg Unload UserForml End Sub

Private Sub OKButton_Click() Dim Msg As String Dim i As Integer

Msg = "You selected" & vbNewLine For i = 0 To ListBoxl.ListCount - 1 If ListBoxl.Selected(i) Then

Msg = Msg & ListBoxl.List(i) & vbNewLine End If Next i MsgBox Msg Unload UserForml End Sub

This routine uses a For-Next loop to cycle though each item in the ListBox. Notice that the loop starts with item 0 (the first item) and ends with the last item (determined by the value of the ListCount property minus 1). If an item's Selected property is True, it means that the list item was selected.

This example is available at this book's Web site.

Was this article helpful?

0 -2

Post a comment