Solved

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

Posted on 2013-06-18
10
888 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

828 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