Microsoft Office Tutorials and References
In Depth Information
In this lesson, you design a UserForm with several controls, including a ListBox that is populated
dynamically with the ability to select multiple items.
To get the sample database files you can download Lesson 19 from the book’s website at
Open a new workbook and activate Sheet1.
In column A, enter the items in the cells as you see them displayed in
Press Alt+F11 to get into the Visual Basic Editor.
Select your workbook name in the Project Explorer, and from the menu
bar click Insert ➪➤ UserForm and accept its default name of UserForm1.
Change the UserForm’s caption property to Shopping List .
Select the UserForm in its design window, and if the Toolbox is not
visible, click View ➪➤ Toolbox.
Draw a ListBox on the UserForm and accept its default name of
ListBox1. Set its MultiSelect property to 1 - fmMultiSelectMulti . 8
Draw a CommandButton on the UserForm below the ListBox and accept
its default name of CommandButton1. Change its
caption property to Transfer selected items to Sheet2
column E .
Draw another CommandButton on the UserForm
below the first CommandButton, and change its
caption property to Exit . That completes the design of
the UserForm, which should resemble Figure 19-16
when it is called.
Double-click the UserForm, which will take you
to its module. Type the code under the UserForm’s
Initialize event that populates the ListBox with
items in column A of Sheet1, ignoring the empty
Private Sub UserForm_Initialize()
Dim LastRow As Long, ShoppingListCell As Range
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For Each ShoppingListCell In .Range(“A1:A” & LastRow)
If Len(ShoppingListCell) > 0 Then ListBox1.AddItem ShoppingListCell.Value