ListBox RowSource Property

[flashrabbit]

I'd like to populate my listbox with values from cells b1:f1. If I put the
following entry into the RowSource property, I only get the value in b1 to
populate my listbox:

sheet1!b1:f1

I've noticed that if the values in the worksheet run down a column instead
of across a row, I get what I want. Thus, RowSource property works with this
entry:

sheet1!a2:a6

Sadly, I need to be able to populate my listbox from cells across a row and
not down a column. Can I adjust my value in the RowSource property to give me
what I want? If not, is there another way to do this? An example would be
nice.

Thanks!

 

[Per Jessen]

Hi

You can populate the ListBox when the userform is initialized, just remember
to delete what you have in the RowSource property now.

Private Sub UserForm_Initialize()
Set SourceRange = Worksheets("Sheet1").Range("B1:F1")
For Each cell In SourceRange
Me.ListBox1.AddItem cell.Value
Next
End Sub

Regards,
Per

 

[Dave Peterson]

You could create a new sheet (hide it???)
and put that data in a vertical list and use that
(maybe even use formulas if the headers can change)

Or you can add the entries via code.

In the userform_initialize event:

dim myCell as range
for each mycell in worksheets("Somesheetname").range("b1:f1").cells
me.listbox.additem mycell.value 'mycell.text????
next mycell