Use horizontal column titles in list box (VBA form)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mr. Smith - 10 Jul 2006 10:18 GMT

Hi.
I have a sheet with 10 data columns with titles like product id, product
name, stock, price etc. I have named the range of titels col_titles.

When I try to assign col_titles as the Row source of a list box in a VBA
form, the titles will not show in a list, but appears when I set the colum
count to 10...

Is there a easy way to transpose a named range, so it will fit as a Row
source for a list box?

Alternative, could anyone give me the correct VBA code to popultae a listbox
based on a horizontal "array" of column titles in a sheet?

Any hints appreciated

Kind regards
Mr. Smith

Reply to this Message

Ryan.Chowdhury@gmail.com - 10 Jul 2006 13:44 GMT

I haven't been able to figure out a way without code. This is an
"event" macro that runs when the listbox is selected:

'ListBox1 is the name of the listbox object

Private Sub ListBox1_GotFocus()
'clear all data in the listbox
ListBox1.Clear

'populate the listbox using the items in range "col_titles"
'loop construct
For i = 1 To Range("col_titles").Columns.Count
ListBox1.AddItem Range("col_titles")(1, i)
Next i

End Sub

> Hi.
> I have a sheet with 10 data columns with titles like product id, product
[quoted text clipped - 14 lines]
> Kind regards
> Mr. Smith

Reply to this Message

Andy Pope - 10 Jul 2006 14:23 GMT

Hi,

You could try this in the initialize event of the userform

' add a row of items as separate row in the listbox
ListBox1.List = _
Application.WorksheetFunction.Transpose(Range("col_titles"))

Cheers
Andy

> Hi.
> I have a sheet with 10 data columns with titles like product id, product
[quoted text clipped - 14 lines]
> Kind regards
> Mr. Smith

Signature

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Reply to this Message

Mr. Smith - 10 Jul 2006 23:06 GMT

Tanks both, Ryan and Andy!
For VBA code example and transpose example on my little case.

It sure helped me out.

Kind regards!!
Mr. Smith

> Hi,
>
[quoted text clipped - 25 lines]
>> Kind regards
>> Mr. Smith

Reply to this Message