listbox rowsource
 

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

listbox rowsource
 

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" wrote:

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

listbox rowsource
 

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
-----Original Message-----
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" wrote:

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


.

listbox rowsource
 

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(Extern al:=true)

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


keepITcool

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


"Christy" wrote:

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
-----Original Message-----
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" wrote:

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


.

listbox rowsource
 

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.


"Christy" wrote in message
...
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
-----Original Message-----
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" wrote:

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


.