Multiple lists in a single ListBox

Free E Books: iqbalkalmati. 474 Part IV: Working with UserForms

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.

Free E Books: iqbalkalmati.

Chapter 14: UserForm Examples 475

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.

Free E Books: iqbalkalmati. 476 Part IV: Working with UserForms

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.

Free E Books: iqbalkalmati.

Chapter 14: UserForm Examples 477

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

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download