Am I Stupid? or is the rowsource ignoring me?

[John T Ingato]

Hers a simple one... for you!

I have a userform with one listbox in it : Userform1.listbox1 See simple code below

If I set the range to A1:A5 it shows all 5 values in the listbox.
If I set the rowsource property to myrange A1:E1, the list box only displays 1 cell... A1

There is not a colsource propety.

Can't I set the source of a listbox to a horizontal range selection? (without tranfering it between an array)

****************************************************

Sub test()

Dim Myrange As Range

Set Myrange = Range("A1", "E1")

Myrange = 0 ' fill 5 cell in 1 row with Zeros

UserForm1.ListBox1.RowSource = Myrange.Address

UserForm1.Show

End Sub

 

[Ron de Bruin]

Hi John

Private Sub UserForm_Initialize()
Dim varr As Variant
varr = Application.Transpose(Worksheets("Sheet1").Range("a1:e1").Value)
Me.ListBox1.List = varr
End Sub

You can use this event in the Userform module.
It will run when you open the Userform

And run this sub in a Normal module

Sub openform()
UserForm1.Show
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




Hers a simple one... for you!

I have a userform with one listbox in it : Userform1.listbox1 See simple code below

If I set the range to A1:A5 it shows all 5 values in the listbox.
If I set the rowsource property to myrange A1:E1, the list box only displays 1 cell... A1

There is not a colsource propety.

Can't I set the source of a listbox to a horizontal range selection? (without tranfering it between an array)

****************************************************

Sub test()

Dim Myrange As Range

Set Myrange = Range("A1", "E1")

Myrange = 0 ' fill 5 cell in 1 row with Zeros

UserForm1.ListBox1.RowSource = Myrange.Address

UserForm1.Show

End Sub

 

[keepitcool]

You cant use rowsource for a transposed range:
so either you have to use a multicolumn listbox
or use list or column instead of rowsource.


if you want columns:
Sub test2()

Dim Myrange As Range
Set Myrange = Range("A1:E1")
Myrange = 0 ' fill 5 cell in 1 row with Zeros
With UserForm1
With .ListBox1
.ColumnCount = Myrange.Columns.Count
.RowSource = Myrange.Address(external:=True)
End With
With .ListBox2
.Column = Myrange.Value
End With
.Show
End With
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >