Microsoft KB Archive/115793

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

Article ID: 115793

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 5.0c

-



This article was previously published under Q115793



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.



WORKAROUND
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
 From the File menu, choose New to start a new workbook. In the range A1:F1 on Sheet1, type the following:

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

 To display the Forms toolbar, choose Toolbars from the View menu, and select the Forms check box, and choose OK. From the Forms toolbar, choose the List Box button. Create a list box on Sheet1. From the Insert menu, choose Macro, and then choose Module to insert a new module sheet in the workbook.  Enter the following macro on the module sheet: Sub AddArrayToListBoxOnWorksheet Sheets("Sheet1").ListBoxes(1).List = Sheets("Sheet1").Range("A1:F1") End Sub  To activate Sheet1, click on the Sheet1 tab.</li> 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.</li></ol>

To use a horizontal range of values in a list box on a dialog sheet
<ol> From the File menu, choose New to start a new workbook.</li> Type the following in cells A1:F1 on Sheet1:

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

</li> From the Insert menu, choose Macro, and then choose Dialog to insert a new Dialog sheet in the workbook.</li> Select the List Box tool from the Forms toolbar. Draw a list box on the sheet Dialog1.</li> From the Insert menu, choose Macro, and choose Module to insert a new Module.</li>  Enter the following macro on the module sheet: Sub AddArrayToListBoxOnDialogSheet Sheets("Dialog1").ListBoxes(1).List = Sheets("Sheet1").Range("A1:F1") End Sub </li> Activate the Dialog1 sheet by clicking on the tab for Dialog1.</li> 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.</li></ol>

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

To use a horizontal range of 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

Additional query words: 5.00c XL5

Keywords: kbprogramming KB115793

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.