listbox rowsource

[Christy]

Hi All,

When setting the RowSource at design time, how do you
specify a certain worksheet range. I can get it work by
just using the range a2:e40 but the values come from
sheet1 and need them to come from sheet2.

Thanks in advance for any help.

Christy

 

[keepitcool]

Private Sub UserForm_Initialize()
With Me.ListBox1
.RowSource = Range("sheet1!a1:b5").Address(External:=True)
End With
End Sub

keepITcool

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

 

[Christy]

Thanks keepITcool but I couldn't get that to work either.
I tried:

Private Sub UserForm_Initialize()

With Me.lbPending
.ColumnCount = 3
.ColumnHeads = True
.ColumnWidths = "50;160;50"
.RowSource = Range("sheet2!c2:e40").Address
(External:=True)
End With

End Sub

and I got "Method 'Range' of object'_Global' failed
?????????? any ideas

Thanks

 

[keepitcool]

Christy,

don't know the rest of your code... and maybe your form gets initialized
from the taskbar while another book is active or maybe it's an addin...

As usual making it a fully qualified reference to the range object
should solve it:

either
ThisWorkbook.Worksheets(y).Range(z).Address(External:=true)

or
Workbooks(x).Worksheets(y).Range(z).Address(External:=true)


keepITcool

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

 

[Tim Zych]

RowSource accepts a string, so you could modify it to just:

..RowSource = "Sheet2!C2:E40"

or if you are using a range object:
Dim rng as range
Set rng = Worksheets("Sheet2").Range("C2:E40")
Me.lbPending.RowSource = rng.Address(External:=True)

Or, to set a permanent link, in the VBE type in Sheet2!C2:E40 in the
properties box. Setting it in VBA creates a temporary link, until the form
is unloaded.