Sheet Range Help Required

[andym]

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

 

[Ron de Bruin]

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

 

[dolivastro]

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]

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

 

[andym]

Thanks Dom,

have done as you suggested, plus stopped screenupdating, so all is
well.

It's getting late here (12:46am), and I have banging my head against
the wall thinking that I could do this without selecting the other
sheet!!!!

Time for bed now all is working!!!

Thanks to you and Ron...


Regards,

andym...

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

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