Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error referencing linked dynamic range | Links and Linking in Excel | |||
Referencing cell in active row | Excel Worksheet Functions | |||
referencing the active cell | Excel Programming | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Setting a range value to the last cell in active worksheet. | Excel Programming |