listbox rowsource

  • Thread starter Thread starter Christy
  • Start date Start date
C

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
 
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
 
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 >
 
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.
 
Back
Top