Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Range Help Required
Dear All,
could somebody please help me in regards to what I am doing wrong? I have a worksheet_change event that when a cell value is changed it runs some code to update some tables on another worksheet (the calc sheet). However, if I am actually in the other (calc) sheet (which is not my intention) then my code works fine. But because I don't want to physically move (or activate or select) this calc sheet I am having problems correctly referencing my code. The code is sitting in a module of the Workbook, not in a specific worksheet. I get a "Select Method of Range Class failed" message in the below code... Sub doCalcs() Dim ws As Worksheet Dim rStart As Range Dim tStart As Range Dim tTeam As Range Set ws = ThisWorkbook.Sheets("Calcs") Set rStart = ws.Range("B4") Set tStart = ws.Range("E4") Set tTeam = ws.Range("J4") rStart.select 'this is where I am getting the error... .....more code End Sub What do I need to select the cell "B4" is selected in the "Calcs" sheet? many thanks, andym |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Range Help Required
You can't select a cell in a sheet that is not active
Why do you want to select it ? What do you want to do -- Regards Ron de Bruin http://www.rondebruin.nl "andym" wrote in message oups.com... Dear All, could somebody please help me in regards to what I am doing wrong? I have a worksheet_change event that when a cell value is changed it runs some code to update some tables on another worksheet (the calc sheet). However, if I am actually in the other (calc) sheet (which is not my intention) then my code works fine. But because I don't want to physically move (or activate or select) this calc sheet I am having problems correctly referencing my code. The code is sitting in a module of the Workbook, not in a specific worksheet. I get a "Select Method of Range Class failed" message in the below code... Sub doCalcs() Dim ws As Worksheet Dim rStart As Range Dim tStart As Range Dim tTeam As Range Set ws = ThisWorkbook.Sheets("Calcs") Set rStart = ws.Range("B4") Set tStart = ws.Range("E4") Set tTeam = ws.Range("J4") rStart.select 'this is where I am getting the error... ....more code End Sub What do I need to select the cell "B4" is selected in the "Calcs" sheet? many thanks, andym |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Range Help Required
Thanks Ron...
The value of the cell in the initial page sets off the code I have part copied in below. This value is copied to the calcs page. The remainder of the code does a complicated lookup (via looping in a large table) based upon the new value. The strings returned from the lookup are then added to a secondary table which feeds back into the initial table. The look up and returning of values works fine. It's just been able to kick the process off from another sheet. Looks like I will have to turn off the screenupdating, select the calcs sheet, run the code, then return back to the initial sheet without seeing the calcs sheet. Would you suggest that also?? Regards, andym... Ron de Bruin wrote: You can't select a cell in a sheet that is not active Why do you want to select it ? What do you want to do -- Regards Ron de Bruin http://www.rondebruin.nl "andym" wrote in message oups.com... Dear All, could somebody please help me in regards to what I am doing wrong? I have a worksheet_change event that when a cell value is changed it runs some code to update some tables on another worksheet (the calc sheet). However, if I am actually in the other (calc) sheet (which is not my intention) then my code works fine. But because I don't want to physically move (or activate or select) this calc sheet I am having problems correctly referencing my code. The code is sitting in a module of the Workbook, not in a specific worksheet. I get a "Select Method of Range Class failed" message in the below code... Sub doCalcs() Dim ws As Worksheet Dim rStart As Range Dim tStart As Range Dim tTeam As Range Set ws = ThisWorkbook.Sheets("Calcs") Set rStart = ws.Range("B4") Set tStart = ws.Range("E4") Set tTeam = ws.Range("J4") rStart.select 'this is where I am getting the error... ....more code End Sub What do I need to select the cell "B4" is selected in the "Calcs" sheet? many thanks, andym |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Range Help Required
Make the sheet active before the select the cell in the sheet. In your
case, do the following: dim s as Excel.Worksheet set s = activesheet ThisWorkbook.Sheets("Calcs").Activate [...] s.Activate ' Just to return to the original sheet. Hope this helps, Dom andym wrote: Dear All, could somebody please help me in regards to what I am doing wrong? I have a worksheet_change event that when a cell value is changed it runs some code to update some tables on another worksheet (the calc sheet). However, if I am actually in the other (calc) sheet (which is not my intention) then my code works fine. But because I don't want to physically move (or activate or select) this calc sheet I am having problems correctly referencing my code. The code is sitting in a module of the Workbook, not in a specific worksheet. I get a "Select Method of Range Class failed" message in the below code... Sub doCalcs() Dim ws As Worksheet Dim rStart As Range Dim tStart As Range Dim tTeam As Range Set ws = ThisWorkbook.Sheets("Calcs") Set rStart = ws.Range("B4") Set tStart = ws.Range("E4") Set tTeam = ws.Range("J4") rStart.select 'this is where I am getting the error... ....more code End Sub What do I need to select the cell "B4" is selected in the "Calcs" sheet? many thanks, andym |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Required data in one sheet | Excel Worksheet Functions | |||
How can I transfer a required data from sheet 1 to sheet 2 automat | Excel Discussion (Misc queries) | |||
IF statement value range required | Excel Discussion (Misc queries) | |||
Required Sheet and fields | Excel Programming | |||
Superformula required for looking up Duplicates in a range | Excel Programming |