How to add Values to ListBox.ColumnHeads

[herbie226]

Hello All,

I really thought this was going to be easy but I am surprised to find that I'm racking my brain and there doesn't seems to be any solutions online - at least not what I have been able to find.

How can I add the the values in row 1 to the headers row in the image below (into the empty boxes)?

Image1.JPG

My code is below.

RowCount = Sheets("Action Steps ListBox").Range("A1048576").End(xlUp).Row
    ListBox1.List = Sheets("Action Steps ListBox").Range("A1:F" & RowCount).Value
    Me.ListBox1.ColumnWidths = "20;60;60;60;60;60"

I am currently using a range to add the values to the listbox but Ideally I want to just have some function to add labels.
i.e.

Me.ListBox1.List(0,0) = "#"

But i realize that is will yield the same result I am getting - meaning, the use of "Me.ListBox1.List(0,0)" will add to row 1 and not the header column!

I want to say that there has to be some function like:

Me.ListBox1.ColumnHeads(0,0).value = "#"

for example, but I haven't found anything like this. AND, I guess I don't know what the ".ColumnHeads" functionality is used for if the cells will be empty - seems useless.

I have tested using the source data in the worksheet as a Table and then the column headers are added correctly, but I have found in the past that userforms interacting with Tables can bog down the program and cause problems, so I would rather not go that route.

Any help would be greatly appreciated!

Thanks,

Jason

[dOOb]

set ColumnHeads to True and RowSource to start from row immediately below the headers row

[herbie226]

Sorry, I could not get that to work. But, I really don't want to use the workbook to provide the source data. Any way to add the header labels as I mentioned in my post?

Thanks for your time, dOOb.

[Norie]

herbie226

The only way to add 'real' column headers is to populate the listbox using the RowSource property and setting ColumnHeads to True.

There are ways you could add your own 'headers', for example you could add a set of labels above the listbox.

[dOOb]

Assuming your data is in range A1:D4 where A1:D1 are headers and your ListBox has 4 columns, then to display these headers in your ListBox the way you want, the RowSource property of the ListBox should point to range "A2:D4" and the ColumnHeads property should be set to True.

That grid at the very top of your ListBox gets populated with headers only if you are are populating your ListBox from range/table on worksheet. It does not work if you are filling your ListBox in from array or adding items to it one by one.

Possible workarounds:
1. Collect your ListBox items in a worksheet, edit headers if needed, then load the ListBox from worksheet.
2. Collect your ListBox items in array, when ready, dump the array into worksheet, adjust headers if needed and load the ListBox from worksheet.
3. On UserForm, add Labels/TextBoxes above ListBox.
4. On UserForm, add separate ListBox above the existing one and load it with headers from worksheet.

[herbie226]

Thank you, dOOb and Norie.

Really wished there was another way but at least this clears everything up for me. I am very appreciative for your time and effort.

Jason