Multiple lists in a single ListBox
[Pages:584]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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- visual basic objects and collections
- multiple lists in a single listbox
- multiselect list boxes in visual basic 6
- userforms quant finance
- excel for dummies drop down list 2013 macro
- vba macros and the visual basic editor john wiley sons
- introduction to visual basic
- dependent drop down lists isle of wight primary care trust
- lesson 15 visual basic cad
Related searches
- standard deviation of a single point
- list of lists in python
- grammar for lists in sentences
- multiply two lists in python
- lists after a colon
- energy of a single photon
- average income of a single american
- linked lists in java
- how to do lists in python
- python printing lists in columns
- how to multiply lists in python
- multiple possessives in a row