XL: Visual Basic Macro to Dynamically Populate a List Box

ID: 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 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/overview/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.

REFERENCES

"Visual Basic User's Guide," version 5.0, pages 219-239

"Microsoft Excel User's Guide," version 5.0, pages 142-143

Additional query words:

Keywords : kbcode kbprg PgmHowto
Version : MACINTOSH:5.0; WINDOWS:5.0,7.0; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbhowto