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?
Private Sub Worksheet_Activate()
Monitored = Range("A76").Value 'Read in value prior to any changes
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
If ActiveChart Is Nothing Then
MsgBox "Please select the chart to modify first."
'.MinimumScale = [xMin]
'.CrossesAt = [xMin]
'.MaximumScale = [xMax]
.MinimumScale = [PriYMin]
.CrossesAt = [PriYMin]
.MaximumScale = [PriYMax]
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = [SecYMin]
.CrossesAt = [SecYMin]
.MaximumScale = [SecYMax]
'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
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
'Reset Variable with new monitored value
Monitored = Range("A76").Value
Application.EnableEvents = True