Solved

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

Posted on 2013-06-18
10
860 Views
Last Modified: 2013-06-19
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

0
Comment
Question by:Jorgen
  • 5
  • 5
10 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39257712
Sounds silly - but look at your line 50: it looks at "E8" not "A76". I think it is as simple as that.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39257726
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
 
LVL 4

Author Comment

by:Jorgen
ID: 39258418
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39258527
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
 
LVL 4

Author Comment

by:Jorgen
ID: 39258535
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Author Comment

by:Jorgen
ID: 39258547
Hi again,

here is some test data

regards

Jørgen
Experts-Exchange-Ressource-Ramp-.xlsm
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39258598
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
 
LVL 4

Author Comment

by:Jorgen
ID: 39259094
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
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 39259115
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
 
LVL 4

Author Closing Comment

by:Jorgen
ID: 39259169
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
text replacement instead of two macros only one macro 2 19
Office 2016 Excel Issue 4 26
Excel 2016 Not Responding Issues 6 27
Boolean help 6 27
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now