Solved

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

Posted on 2013-06-18
10
844 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

758 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

21 Experts available now in Live!

Get 1:1 Help Now