The information in this article applies to:
- Microsoft Excel for Windows, version 5.0
- Microsoft Excel for Windows NT, version 5.0
- Microsoft Excel for the Macintosh, version 5.0
- Microsoft Excel for Windows 95, version 7.0
SUMMARY
The following macro dynamically populates a list box with a group of sub
categories based on the contents of the first list box.
The following macros can be used to display a general category in one list
box and then dynamically populate the second list box with items related to
that general category in a second list box.
MORE INFORMATION
Microsoft provides programming examples for illustration only, without
warranty either expressed or implied, including, but not limited to, the
implied warranties of merchantability and/or fitness for a particular
purpose. This article assumes that you are familiar with the programming
language being demonstrated and the tools used to create and debug
procedures. Microsoft support engineers can help explain the functionality
of a particular procedure, but they will not modify these examples to
provide added functionality or construct procedures to meet your specific
needs. If you have limited programming experience, you may want to contact
the Microsoft fee-based consulting line at (800) 936-5200. For more
information about the support options available from Microsoft, please see
the following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/refguide/default.asp
To dynamically populate a list box, follow these steps:
- Open a new workbook by clicking New on the File menu.
- Create a dialog sheet with two list boxes. Name one list box 5 and the
other list box 6.
- On Sheet1, enter the following data in cells A1:A4:
A1: Alabama
A2: Arizona
A3: Arkansas
A4: Texas
- On Sheet2, enter the following information in cells A1:D4:
A1: Alabama 1 B1: Arizona 1 C1: Arkansas 1 D1: Texas 1
A2: Alabama 2 B2: Arizona 2 C2: Arkansas 2 D2: Texas 2
A3: Alabama 3 B3: Arizona 3 C3: Arkansas 3 D3: Texas 3
A4: Alabama 4 B4: Arizona 4 C4: Arkansas 4 D4: Texas 4
- After you enter the data on sheet 2, give each column of data a
defined name based on the name of the respective state entered on
Sheet1. For example, select the range A1:A4 on Sheet2, and point to
Name on the Insert menu, and click Define. Give the range the defined
name "Alabama."
- Insert a new module sheet and type the following:
' This procedure fills the first list box with names of the states
' as entered on Sheet1.
Sub Fill_FirstListbox()
' Fill the first list box.
DialogSheets("dialog1").ListBoxes("list box 5").ListFillRange _
="sheet1!a1:a" & Worksheets("sheet1").Range("a1").End(xlDown).Row
' Display the dialog sheet.
DialogSheets("dialog1").Show
End Sub
' Macro to populate the second list box.
Sub statemaps()
' Activate the worksheet containing the data for the second
' list box.
Worksheets("sheet2").Activate
' The following line returns the value of the selected item in the
' first list box.
x = DialogSheets("dialog1").ListBoxes("list box 5") _
.List(DialogSheets("dialog1").ListBoxes("list box 5").ListIndex)
' Select the range based on the first list box.
Range(x).Select
' Assign the selected range address to a variable.
y = Selection.Address
' Fill the second list box.
DialogSheets("dialog1").ListBoxes("list box 6"). _
ListFillRange = "sheet2!" & Range(y).Address
End Sub
- Assign the macro statemaps() to list box 5. To do this, click list box 5
on the dialog sheet, click Assign Macro on the Tools menu. Click
statemaps() in the dialog box that appears.
REFERENCES
"Visual Basic User's Guide," version 5.0, pages 219-239
"Microsoft Excel User's Guide," version 5.0, pages 142-143