Populating List Box Header Not Working

Question

If you set the ColumnHeads property for a ListBox to TRUE, you will see a small grid at the top of the listbox intended to display a header row for the data.

The way it is supposed to work is, it takes any row above the range being used for the ListBox, and populates this header row with that data.

I am told that this only works when using theListBox.RowSource property, however, RowSource does not work with Excel 2011 Mac, so I cannot test this out.

Apparently, you have to use the ListBox.List property when working with a ListBox, which does work for populating the ListBox, but does NOT work for populating the Header.

You are left with a blank header.

Does anyone have an idea of how to get this to work?

Answer 1

I'm quite sure the answer is pretty simple - you can't on a Mac. the rowsource and columnsource properties are not supported. Likewise listbox headers are not supported in Mac VBA.

Answer 2

Please take a moment to let Microsoft know what sort of impact this has on your work. Go to Excel's Help menu and choose the Send Feedback option. Describe in a business-like way how this shortcoming affects your business case.

Feedback here in the forums is not generally read by Microsoft. Feedback sent via the feedback form is read and cataloged.

Answer 3

All too common, unfortunately. Mac users are shortchanged all the time in VBA. As for Headers themselves, that property does exist and they do appear when set to True in Properties, but they cannot be populated, hence are worthless. So, VBA for Mac does have Headers for ListBoxes, it's just that they do nothing!

Answer 4

Thanks for the reply. I understand the labels-on-top-of-the-listbox workaround, but I was trying to get the Headers working as they are intended - obviously enabling the Headers in the Listbox properties is there for a reason.

There is no RowSource property available for a ListBox that I can find in Excel 2011 Mac, and using that property in VBA code results in an error. That is precisely the problem I am posting about.

Answer 5

You cannot populate those headers in a listbox in VBA.

If you want to use those headers, you need to specific the "RowSource" of the listbox and refer that to a range in a worksheet. (e.g. you got a named range called "MyRange". Put that name into "Row Source".) The row immediately above that range will be used as the headers.

Therefore, with VBA, the normal practice, is to use labels. You put some labels above the listbox, and type your headers there.

Regards,

Edwin Tam

Excel Power Expander - http://www.vonixx.com