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

Open in new window

LVL 4
JorgenAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
andrewssd3Connect With a Mentor Commented:
OK - sorry I didn't get that.  In that case just remove the line
If Application.Intersect(Target, Range("A76")) Is Nothing Then Exit Sub

Open in new window

.  That is what is stopping it working unless someone actually types in A76.  Without that A76 can contain a formula and your code will still fire.

Stuart
0
 
andrewssd3Commented:
Sounds silly - but look at your line 50: it looks at "E8" not "A76". I think it is as simple as that.
0
 
andrewssd3Commented:
Also in a Worksheet_Change event handler, it's usual to do a quick check at the top of the routine that the changed cell(s) are in the range you're interested in:
If Application.Intersect(Target, Range("A76")) Is Nothing Then Exit Sub

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
JorgenAuthor Commented:
Hi Andrew,

I found the first error, and corrected that

The Other one I will check as soon as I get to the clients site.

And I have seen something similar in parts of code from others regarding this solution, so you might be right.

I just could not see any need for this

regards

Jørgen
0
 
andrewssd3Commented:
You are right about the second thing, it's not an error anyway, it's just that sometimes it can avoid you having to execute a lot of unnecessary code.  In your case very little happens as the value will match so you will skip the processing anyway.  I was just giving you some general advice about Worksheet_Change routines.
0
 
JorgenAuthor Commented:
Unfortunately it did not give any change.

If you need, I will prepare a test file, so  you can try it out. The new scale procedure still works fine, and unfortunately I have no experience with worksheet_change routines.

regards

Jørgen
0
 
JorgenAuthor Commented:
Hi again,

here is some test data

regards

Jørgen
Experts-Exchange-Ressource-Ramp-.xlsm
0
 
andrewssd3Commented:
Your worksheet_activate and worksheet_change routines need to be in the module for the sheet they relate to.  You'll see here I have moved the code into the worksheet, and made Monitored public so it can be seen by all the routines.  Appears to work OK now.

StuartExperts-Exchange-Ressource-Ramp-.xlsm
0
 
JorgenAuthor Commented:
Hi Stuart,

I agree, that it works, if I manually changes the value of the cell. But as you can see in my original sheet, I need it to work based on changes in the value, but there is a formula that is calculated based on the changed graph series. This is crucial as the users should not have any work changing this

regards

Jørgen
0
 
JorgenAuthor Commented:
Hi Stuart,

It works like magic, and I actually both learned how to create flexible values in the primary and secondary y axis as well as how to get the flexibility of a cell update.

regards

Jørgen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.