Dynamic Macro That Auto Updates Graph Scale Misfiring When File Opens

Question

I have an issue that makes no sense to me.

Option Explicit

Private Sub Worksheet_Calculate()

Dim Chtob As ChartObject
Dim wks As Worksheet
Set wks = ActiveSheet

On Error GoTo Terminate

For Each Chtob In ActiveSheet.ChartObjects
With Chtob.Chart
    If wks.Range("$G$2").Value <> .Axes(xlCategory).MaximumScale Then
        .Axes(xlCategory).MaximumScale = wks.Range("$G$2").Value
    End If
    If wks.Range("$C$2").Value <> .Axes(xlCategory).MinimumScale Then
        .Axes(xlCategory).MinimumScale = wks.Range("$C$2").Value
    End If
    If wks.Range("$G$2").Value <> .Axes(xlCategory, xlSecondary).MaximumScale Then
        .Axes(xlCategory, xlSecondary).MaximumScale = wks.Range("$G$2").Value
    End If
    If wks.Range("$C$2").Value <> .Axes(xlCategory, xlSecondary).MinimumScale Then
        .Axes(xlCategory, xlSecondary).MinimumScale = wks.Range("$C$2").Value
    End If
End With
Next

Exit Sub
Terminate:
MsgBox "Storm Event Not Valid, Please check if such event number exists"
End

Exit Sub

This macro is used on a tab that has two charts. When a certain cell changes the macro updates the graph scale. This tab will be then duplicated numerous times to show different time events.
The issue arises when someone else tries to open this file. The moment the file is open they get the error to pop up as many times as the amount of tabs created. This for some reason causes a different tab with a different graph to reset it's x scale. This different tab does not have the dynamic macro attached to it and no other macros are being used.

I want to say that a different version of Excel might be part of the problem, but there are times when this doesn't happen.

The way it should work is when somebody enters the wrong value in cell B2 the macro can't execute. So instead of going into debug, one gets an error message. So I need the error portion of the macro to be there.

I should mention that the tab also has another dynamic macro that automatically renames the tab name if the same cell changes.

Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
    If Not Target.HasFormula Then
        If Not Target.Value = vbNullString Then
             On Error GoTo ErrHandler
             ActiveSheet.Name = "Event" & " " & Target.Value
        End If
    End If
End If
Exit Sub
ErrHandler:
MsgBox "Error " & Err & ":" & Error(Err)
On Error GoTo 0
End Sub

two immediate things that may help 1) in `Worksheet_Calculate` change `Set wks = ActiveSheet` to `Set wks = Me` 2) In `Worksheet_Change` change `ActiveSheet.Name` to `Me.Name` (or `Target.Parent.Name`). Using `ActiveSheet` is dangerous in the sense that the `ActiveSheet` may not be what you expect it to be. This is a [**must read**](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for VBA developers. — Scott Holtzman, Feb 12 '16 at 21:10
If the macro running as soon as the file is opened is causing issues, don't use the `Worksheet_Calculate()` event. — Kyle, Feb 12 '16 at 22:04
@ ScottHoltzman I heard about the ActiveSheet problems. It's just that the name of my sheet was not set in stone and would change each time as the cell that updates the graph scale changes. So wks = Me sets me to activesheet. Will definitely keep that in mind and try your changes. Will have to wait for my coworkers to try out. — Dan, Feb 12 '16 at 22:37
Probably due to a copy/paste issue, but your first bit of code is missing the `Terminate` label... Otherwise, it's very difficult to provide any guidance when we don't know what the error was. — ARich, Feb 13 '16 at 06:30
@ARich yes sorry about that, it was a copy and paste error. So the reason I put in `On Error GoTo` is the way the sheet is set up is one enters an event number in cell `B2` and then cells `C2` and `G2` have a `vlookup` formula and use cell `B2` to find the correct start and end dates. Now if one inputs a number that doesn't exists the start and end dates become `#NA` and one gets a `Runtime error 13: Type mismatch`. Now when someone opened the spreadsheet my Input error code would fire off and a hole different graph would get its x scale reset. — Dan, Feb 14 '16 at 02:27

score 0 · Accepted Answer · answered Jun 23 '16 at 18:25

Thanks to comments made by Scott, my issue has never poped up again I just changed Set wks = Activesheet to Set wks = Me and then changed all of the wks to Me in the script

Option Explicit

Private Sub Worksheet_Calculate()

Dim Chtob As ChartObject
Dim wks As Worksheet
Set wks = Me

On Error GoTo Terminate

For Each Chtob In Me.ChartObjects
    With Chtob.Chart
  If wks.Range("$G$2").Value <> .Axes(xlCategory).MaximumScale Then
    .Axes(xlCategory).MaximumScale = wks.Range("$G$2").Value
  End If
  If wks.Range("$C$2").Value <> .Axes(xlCategory).MinimumScale Then
    .Axes(xlCategory).MinimumScale = wks.Range("$C$2").Value
        End If
  If wks.Range("$G$2").Value <> .Axes(xlCategory, xlSecondary).MaximumScale Then
    .Axes(xlCategory, xlSecondary).MaximumScale = wks.Range("$G$2").Value
  End If
  If wks.Range("$C$2").Value <> .Axes(xlCategory, xlSecondary).MinimumScale Then
    .Axes(xlCategory, xlSecondary).MinimumScale = wks.Range("$C$2").Value
        End If
    End With
Next

    Exit Sub
Terminate:
    MsgBox "Storm Event Not Valid, Please check if such event number exists"
    End

End Sub

Dynamic Macro That Auto Updates Graph Scale Misfiring When File Opens

1 Answers1