XL5: Can't Use Horizontal Array for List Box/Drop-Down Input

• Microsoft Excel for Windows, version 5.0, 5.0c

SUMMARY

In Microsoft Excel version 5.0, you cannot use a horizontal array for the input range for list boxes or drop-down boxes. When you use a horizontal range for the input range of a list box or a drop-down box, you will not receive an error message; however, only the first item in the array will be displayed in the list box or drop-down box.

Note also that you cannot use the ListFillRange property to fill a list box or drop-down box with a row of data. For information about how to populate a list box or drop-down box with a row of data, see the sample macros at the end of this article.

WORKAROUNDS

Microsoft provides examples of Visual Basic procedures 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 Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

To use a horizontal range of values in a list box on a worksheet

1. From the File menu, choose New to start a new workbook.

2. In the range A1:F1 on Sheet1, type the following:

         A1: Apple B1: Banana C1: Orange D1: Peach E1: Pear F1: Watermelon
   

3. To display the Forms toolbar, choose Toolbars from the View menu, and select the Forms check box, and choose OK.

4. From the Forms toolbar, choose the List Box button. Create a list box on Sheet1.

5. From the Insert menu, choose Macro, and then choose Module to insert a new module sheet in the workbook.

6. Enter the following macro on the module sheet:

      Sub AddArrayToListBoxOnWorksheet()
        Sheets("Sheet1").ListBoxes(1).List = Sheets("Sheet1").Range("A1:F1")
      End Sub
   
   

7. To activate Sheet1, click on the Sheet1 tab.

8. From the Tools menu, choose Macro. In the list of macros, select AddArrayToListBoxOnWorksheet, and choose the Run button to fill the list box with the horizontal array of values in A1:F1.

1. From the File menu, choose New to start a new workbook.

2. Type the following in cells A1:F1 on Sheet1:

         A1: Apple B1: Banana C1: Orange D1: Peach E1: Pear F1:Watermelon
   

3. From the Insert menu, choose Macro, and then choose Dialog to insert a new Dialog sheet in the workbook.

4. Select the List Box tool from the Forms toolbar. Draw a list box on the sheet Dialog1.

5. From the Insert menu, choose Macro, and choose Module to insert a new Module.

6. Enter the following macro on the module sheet:

      Sub AddArrayToListBoxOnDialogSheet()
         Sheets("Dialog1").ListBoxes(1).List = Sheets("Sheet1").Range("A1:F1")
      End Sub
   
   

7. Activate the Dialog1 sheet by clicking on the tab for Dialog1.

8. From the Tools menu, choose Macro. From the list of macros, select AddArrayToListBoxOnDialogsheet and choose the Run button to fill the list box with the horizontal array of values in A1:F1.

To use a horizontal range of values in a drop-down list box on a worksheet

Sub AddArrayToDropDownOnWorksheet()
  Sheets("Sheet1").DropDowns(1).List = Sheets("Sheet1").Range("A1:F1")
End Sub

values in a drop-down list box on a dialog sheet

Sub AddArrayToDropDownOnDialogSheet()
  Sheets("Dialog1").DropDowns(1).List = Sheets("Sheet1").Range("A1:F1")
End Sub