dynamically changing chart y axis using spin buttons and worksheet_calculate event

[jimnwilson]

Hi,

I have a multi sheet workbook that has spin buttons on two of th
sheets. I use one sheet with a chart that has its y axis min and ma
scaling updated from data recalculated after I use the spin button. Th
code works fine until I use a spin button on another sheet, which seem
to trigger the worksheet_calculate event from the other sheet with th
chart. I then get an error message because the code can't find th
chart. I need to know how to disable this code when I am working on th
other sheet.

I am not a programmer by any stretch, so any help would be appreciated
Here is the code from the main worksheet, all of which I obtained b
web searches (I'm learning!) :

==============================================
Private Sub Worksheet_Calculate()

mySelection = ActiveWindow.RangeSelection.Address
UpdateScale
Range(mySelection).Select

End Sub

Sub UpdateScale()
'
' UpdateScale Code
' by Ray Blake
'
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("$J$2").Value
.MaximumScale = Range("$I$2").Value
.MajorUnit = (.MaximumScale - .MinimumScale) / 10

End With

End Su

 

[MSP77079]

The easiest thing to do would be to put the command

On Error goto EndMacro

and then just before the End Sub line put this label
EndMacro:

The error is occurring because there is no Chart1 to select on th
other sheet, right?

The second easiest thing to do would be to put the entire macro insid
an if statement:

If ActiveSheet.Name <> "whatever the name of the sheet with the char
is" then

put all your stuff in here

End i

 

[jimnwilson]

Thanks MSP77089,

Here is what I did as per your help:
============================================
Private Sub Worksheet_Calculate()

If ActiveSheet.Name = "Sheet1" Then

mySelection = ActiveWindow.RangeSelection.Address
UpdateScale
Range(mySelection).Select

End If

End Sub
============================================
It stopped the error from the spin button on the other sheet, but no
the main sheet doesn't work. And it is "Sheet1". Am I doing somethin
stupid