Macro to hide button creates error in every other spreadsheet

[Tarver]

I have a little forecast tool that allows people to make changes to a forecast to see the expected result. When a change is made, I have code that reveals a button to reset the formulas when the user is ready, pasted below. I have a formula in cell E5 that checks to see if several other cells are formulas. When they are not formulas, my E5 value changes to 1, and the button appears. Clicking on the button runs a macro that restores the formulas.

My problem is that when ANY OTHER spreadsheet is open, this code errors out. It tries to run no matter what sheet is being changed. The code should ONLY run when a change is made on a tab labeled "DENOM FORECAST." I've tried to limit it to just that tab, to no avail.

Can this code be modified to limit when it executes, or is there another way to show and hide a button when a value on a sheet changes?

Private Sub Worksheet_Calculate()
Static OldVal As Variant
If Range("E5").Value <> OldVal Then
    OldVal = Range("E5").Value
End If
If OldVal = 0 Then
    Worksheets("Denom Forecast").Shapes("ResetButton").Visible = False
Else:
    Worksheets("Denom Forecast").Shapes("ResetButton").Visible = True
End If
End Sub

'Private Sub Worksheet_Change(ByVal Target As Range)
'    If Target.Row = 5 And Target.Column = 5 Then _
'        Me.Shapes("ResetButton").Visible = (Cells(5, 5).Value = 1)
'End Sub

 

[rlv01]

Some questions:

I have a formula in cell E5

1. What sheet is cell E5 located in?

2. Where (what code module) is your code sample located?

3. Why are you using this:

Private Sub Worksheet_Calculate()

instead of this:

Private Sub Worksheet_Change(ByVal Target As Range)

 

[Tarver]

Thanks for your response! Answers to your questions are below:

1. Cell E5 is in the "Denom Forecast" tab.

2. The code is in the Denom Forecast worksheet (which is Sheet3 in the VBA project.

3. I'm using that code instead of your alternative because I thought that was the right method to look for the change in cell E5.

I've made the change you suggested in your third question, and it seems like it's the solution I needed! The sheet appears to work as intended now, regardless of how many other things are open.

Thank you very much for taking the time!

 

[rlv01]

Glad you got it sorted out.