Range error when referencing non active worksheet
 

I get an error in this Subroutine at the line marked , it works fine
if I activate the worksheet first and remove the "Worksheets(1)." from
the code.

Option Explicit
Dim Rng As Range
Dim Rw As Long

Private Sub UserForm_Activate()
Rw = Worksheets(1).Range("A65536").End(xlUp).Row
Set Rng = Worksheets(1).Range(Cells(2, 2), Cells(Rw, 2))
ComboBox1.RowSource = Rng.Address
End Sub

Any idea why this is happening ?

Thanks
Sal

Range error when referencing non active worksheet
 

Sal,

Cells(x, x) always refers to the active sheet.
So you should prefix cells with the sheet name if you are
referring to another sheet...
'(notice the dots)

With Worksheets(1)
Rw = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range(.Cells(2, 2), .Cells(Rw, 2))
End With

Jim Cone
San Francisco, USA


"SP" wrote in message oups.com...
I get an error in this Subroutine at the line marked , it works fine
if I activate the worksheet first and remove the "Worksheets(1)." from
the code.
Option Explicit
Dim Rng As Range
Dim Rw As Long

Private Sub UserForm_Activate()
Rw = Worksheets(1).Range("A65536").End(xlUp).Row
Set Rng = Worksheets(1).Range(Cells(2, 2), Cells(Rw, 2))
ComboBox1.RowSource = Rng.Address
End Sub

Any idea why this is happening ?

Thanks
Sal

Range error when referencing non active worksheet
 

Hello Sal,

I am still not certain why this happens, but make this change to you
code and it will work.

Set Rng = Worksheets(1).Range(Cells(2, 2).Address, Cells(Rw
2).Address)

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=48029

Range error when referencing non active worksheet
 

Jim,

Thanks for the info, any decent books you know of ? gettin gtired of
hunting for all the details.

Sal

Range error when referencing non active worksheet
 

Sal,

Any of John Walkenbach's "Excel xxx Power Programming with VBA
books would be a good choice - IDG Books.
The Excel version is not that critical at long as it is XL97 or later.
(Don't forget the built-in help)

Regards,
Jim Cone
San Francisco, USA



"SP" wrote in message oups.com...
Jim,
Thanks for the info, any decent books you know of ? gettin gtired of
hunting for all the details.
Sal

Range error when referencing non active worksheet
 

Jim and Leith, I tried both suggestions and both methods eliminated the
error.
However I have multiple ComboBoxes which pull data from different
Worksheets and all the ComboBoxes are puliing the data from
"Worksheets(1)", the row and column paramaters are working propwerly.

The code for the other ComboBox is:

Private Sub cmbxOperation_DropButtonClick()
RwOp = Worksheets("Operation").Range("H65536").End(xlUp). Row
Set RngOp = Worksheets("Operation").Range(Cells(2, 8).Address,
Cells(5, 8).Address)
cmbxOperation.RowSource = RngOp.Address
End Sub

Once again thanks for the help.
Sal

Range error when referencing non active worksheet
 

I moved the lookup lists to the worksheets(1) just to keep going, I
would like to understand what I'm doing wrong.

Sal

Range error when referencing non active worksheet
 

cmbxOperation.RowSource = RngOp.Address(External:=True)

I would suggest using Jim Cones Approach as it should be faster I would
think

Private Sub cmbxOperation_DropButtonClick()
With Worksheets("Operation")
RwOp = .Range("H65536").End(xlUp).Row
Set RngOp = .Range(.Cells(2, 8),.Cells(5, RwOp))
End With
cmbxOperation.RowSource = _
RngOp.Address(External:=True)
End Sub




--
Regards,
Tom Ogilvy




"SP" wrote in message
oups.com...
Jim and Leith, I tried both suggestions and both methods eliminated the
error.
However I have multiple ComboBoxes which pull data from different
Worksheets and all the ComboBoxes are puliing the data from
"Worksheets(1)", the row and column paramaters are working propwerly.

The code for the other ComboBox is:

Private Sub cmbxOperation_DropButtonClick()
RwOp = Worksheets("Operation").Range("H65536").End(xlUp). Row
Set RngOp = Worksheets("Operation").Range(Cells(2, 8).Address,
Cells(5, 8).Address)
cmbxOperation.RowSource = RngOp.Address
End Sub

Once again thanks for the help.
Sal