Use horizontal column titles in list box (VBA form)

[Mr. Smith]

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

 

[Ryan.Chowdhury]

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

 

[Andy Pope]

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

 

[Mr. Smith]

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