XL97: Problems Setting Column Headings in ListBox Control

ID: Q165494

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

If you set the RowSource property of a ListBox control to a range of cells on a worksheet, the column headings in the list box may not be the values in the first row of your data. For example, the column headings in the ListBox control may be "Column A" and "Column B" (without the quotation marks).

CAUSE

You may experience this behavior if all of the following are true:

The range of cells you are using has labels in the first row.
```
    -and-
```
You set the RowSource property of the ListBox control to the entire range of your data, including the row with the labels.
```
    -and-
```
You set the ColumnHeads property of the ListBox control to True.

RESOLUTION

To have the labels appear as column labels in a ListBox control, set the RowSource property of the ListBox to the range of cells that is beneath the labels. Please see the "More Information" section for a sample UserForm that demonstrates this behavior.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

The following steps demonstrate how to set the properties of a ListBox control so that the first row of data is used for the column labels in the ListBox.

1. Close and save any open workbooks, and then open a new workbook.

2. Enter the following in Sheet1:

   A1: Name   B1: Amount
   A2: Bob    B2: 1
   A3: Sue    B3: 2
   A4: Tom    B4: 3
   A5: Pat    B5: 4

3. Start the Visual Basic Editor (press ALT+F11).

4. In the Visual Basic Editor, click UserForm on the Insert menu.

5. If the Toolbox is not visible, click Toolbox on the View menu. Click

   the ListBox control in the Toolbox, and then draw a ListBox on the
   UserForm.

6. Using the Properties window, set the following properties for the

   ListBox control:

   Property       Value
   --------------------------

   ColumnHeads    True
   ColumnCount    2
   RowSource      Sheet1!A2:B5

7. Run the UserForm. To do this, activate the UserForm, and click Run

   Sub/UserForm on the Run menu.

The labels in A1 and B1 will be the column labels in the ListBox, and the first row of selectable data will be from A2 and B2.

NOTE: If you set the RowSource property to "Sheet1!A1:B5" (without the quotation marks), and then run the UserForm, the data in A1 and B1 will be a selectable row in the ListBox and the column headings will be "Column A" and "Column B", respectively.

REFERENCES

For more information about the ListBox control, click the Index tab in Microsoft Visual Basic for Applications Help, type the following text

   listbox control

and then double-click the selected text to go to the "Things you can do with a multicolumn ListBox or ComboBox" topic.

Additional query words: XL97 list box row source

Keywords          : kbprg kbdta xlvbainfo kbbuglist
Version           : WINDOWS:97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbbug
Solution Type     : kbpending

Last Reviewed: November 2, 1998