[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2013-06-18
10
Medium Priority
?
945 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 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