Using VBA to update graph axis values, when a specific cells value changes

Hi Experts,

I have a procedure (newScale), that works fine. But it would be perfect, if it could automatically change, when the value in cell "A76" changes - and "A76" is based on a formula.

Cell "A76" is calculating the maximum value of the primary and secondary Y Axis by using the max formula on my Graph dataset.

Then I set my max value using the newScale procedure.

But I Can see that It should be possible change the Axis automatically whenever the value in cell "A76" changes. I have found some code on Ozgrid, that I have put into the Worksheet_Change procedure, but for some reason it does not work.

Can anybody tell me what I do wrong?

Option Explicit
Dim Monitored
 
Private Sub Worksheet_Activate()
    Monitored = Range("A76").Value 'Read in value prior to any changes
End Sub

Sub newScale()
Dim PriYMin As Integer
Dim PriYMax As Integer
Dim SecYMin As Integer
Dim SecYMax As Integer

PriYMin = 0
PriYMax = Range("A76").Value
SecYMin = 0
SecYMax = Range("A76").Value

Worksheets("Ressource Data").ChartObjects(1).Activate
'Charts("Charts1").Activate
If ActiveChart Is Nothing Then
MsgBox "Please select the chart to modify first."
Exit Sub
End If
'With ActiveChart.Axes(xlCategory)
'.MinimumScale = [xMin]
'.CrossesAt = [xMin]
'.MaximumScale = [xMax]
'End With
With ActiveChart.Axes(xlValue)
.MinimumScale = [PriYMin]
.CrossesAt = [PriYMin]
.MaximumScale = [PriYMax]
End With
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = [SecYMin]
.CrossesAt = [SecYMin]
.MaximumScale = [SecYMax]
End With
'PriYMin = the minimum value I need on the primary y-axis
'PriYMax = the maximum value I need on the primary y-axis
'SecYMin = the minimum value I need on the secondary y-axis
'SecYMax = the maximum value I need on the secondary y-axis

End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
     'Prevent looping of code due to worksheet changes
    Application.EnableEvents = False
     'Compare monitored cell with initial value
    If Range("E8").Value <> Monitored Then
         'Do things as a result of a change
        Call newScale
         'Reset Variable with new monitored value
        Monitored = Range("A76").Value
    End If
     'Reset events
    Application.EnableEvents = True
End Sub

Select allOpen in new window