Create Static Headers in a listbox

[brodaddy2002]

I am creating a dynamic listbox populates depending on a user selected box. I am aware that setting column headers property = true makes the headers the row one above the rowsource range. My problem is that the row above my dynamic range isn't always my header information.

Question: Is there a way to work around this? I guess that I could just easily apply Label boxes on top of the ListBox, but I just wanted to apply a little more finesse to the situation.

Thanks, I would greatly appreciate anyone's suggestions.

 

[tweedle]

I first thought Union of Ranges would work, but alas, it does not.
I think the answer is simply no.
Using ColumnHeads directs the control to look one cell above the RowSource for a title/label.

Could copy source data + Label to a hidden worksheet and use that as your RowSource.

 

[brodaddy2002]

Yeah, I thought about that, but I don't think that it will give me that much bang for the buck (I have a huge amount of data with 9 separate option buttons.) I was just trying to get fancy. Again, thanks for your help. I think that I will go with the brute force method of just adding the labels on top of the ListBox.


I guess I will keep this post open if anyone else might have ideas, but I think that it is simply one of the few limitations of excel VBA.

 

[tweedle]

another thought - don't know what it may do to your layout - but assuming all your data starts on the same row and titles are just offset somehow - add a row above the data - hide it (RowHeight=0), and include it in the RowSource.

 

[Jon von der Heyden]

Are you using a userform? If you are then perhaps you can copy your list data to a range during the life of the userform class instance? In doing so you can plonk the header in the 1st row and then append the list data directly below it. You could use an existing sheet, or you could even add a sheet. If you use a new {temporary} sheet then tidying up is easy; just delete the sheet on userform hide/unload.