# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Add Listbox header using VBA

## shirley_kee

I have Column A to BH in my excel sheet. I want to display column A, B ,C,E, G and BH in a userform listbox. I've manage to use VBA to add selected column data into the listbox. My problem is I have no idea how to create a list header on top of the list box. I try using the additem but it doesn't work.

My listbox has horizontal scrollbar as the list is too long so I can't use label to create the list header name

Please help...

----------


## Bob Phillips

AFAIK you can't, you can only get headers if you bind the listbox to a
range.

In the  ListBox,
- set the ColumnCount property to number of columns,
- set ColumnHeads to True,
- set RowSource to the range of cells >>below<< the column headers text

The cells above this RowSource range will be used as column headers.

You can set the rowsource in VBA

With Me.ListBox1
.RowSource = Range("A2:A5").Address
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"shirley_kee" <shirley_kee.2a3rqo_1151482202.6764@excelforum-nospam.com>
wrote in message
news:shirley_kee.2a3rqo_1151482202.6764@excelforum-nospam.com...
>
> I have Column A to BH in my excel sheet. I want to display column A, B
> ,C,E, G and BH in a userform listbox. I've manage to use VBA to add
> selected column data into the listbox. My problem is I have no idea how
> to create a list header on top of the list box. I try using the additem
> but it doesn't work.
>
> My listbox has horizontal scrollbar as the list is too long so I can't
> use label to create the list header name
>
> Please help...
>
>
> --
> shirley_kee
> ------------------------------------------------------------------------
> shirley_kee's Profile:
http://www.excelforum.com/member.php...o&userid=30384
> View this thread: http://www.excelforum.com/showthread...hreadid=556357
>

----------


## gtmeloney

If you set the rowsource, are you still able to manually modify the data in the listbox via VBA code? When I try that I get an error, stating "permission denied". This seems logical to me: I linked the listbox values up with a range of cells, so I shouldn't be able to modify otherwise. But is there a way around this?

The only reason that I am even considering using the rowsource technique is to allow me to use the header row, but I have to be able to modify the values otherwise.

----------


## broro183

hi gtmeloney,

Did you realise that the last message in this thread was posted in June of 2006?

The thread is probably too old for the previous posters to even remember, let alone respond to, so I suggest opening a new thread (as per the Forum rules) with your question about potential work-arounds.


hth
Rob

----------


## gtmeloney

Sorry Rob, 

I didn't know that I was breaking the rules. I have decided that its not worth my time to find a workaround, I'll just use a hidden worksheet. Sorry for the trouble.

Greg

----------


## romperstomper

Just for the record, the short answer is: no, you can't. If you bind, you can't change; if you don't (my preference), you can't use the ColumnHeaders.

----------


## broro183

Good as gold, Greg - Romper' seems to have the facts for you so it sounds like mission accomplished  :Smilie:

----------


## johnywhy

> Did you realise that the last message in this thread was posted in June of 2006?



did you realize this is still relevant?
did you realize forum threads are not just for the OP? 
i came here because it is relevant to my work. the technologies involved are still in-use.

----------


## rorya

You missed Rob's point (which you left out of your quote).

----------

