Microsoft KB Archive/141568

{|
 * width="100%"|

XL: Visual Basic Macro to Dynamically Populate a List Box

 * }

Q141568

-

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 professionals 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 a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please see the following page on the World Wide Web:

"http://www.microsoft.com/partner/referral/" For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

"http://support.microsoft.com/directory/overview.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.</li></ol>