Microsoft Office Tutorials and References
In Depth Information
Try It
Try iT
In this lesson, you design a UserForm with several controls, including a ListBox that is populated
dynamically with the ability to select multiple items.
lesson requirements
To get the sample database files you can download Lesson 19 from the book’s website at
www.wrox.com .
step-by-step
1.
Open a new workbook and activate Sheet1.
2.
In column A, enter the items in the cells as you see them displayed in
Figure 19-15.
3.
Press Alt+F11 to get into the Visual Basic Editor.
4.
Select your workbook name in the Project Explorer, and from the menu
bar click Insert ➪➤ UserForm and accept its default name of UserForm1.
5.
Change the UserForm’s caption property to Shopping List .
6.
Select the UserForm in its design window, and if the Toolbox is not
visible, click View ➪➤ Toolbox.
7.
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 .
figurE 19-15
8.
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.
9.
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
cells.
Private Sub UserForm_Initialize()
Dim LastRow As Long, ShoppingListCell As Range
With Worksheets(“Sheet1”)
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
figurE 19-16
 
Search JabSto ::




Custom Search