Solved

secondary axis maximum to track primary axis

Posted on 2011-02-10
7
310 Views
Last Modified: 2012-05-11
I have a clustered stacked chart (a la Peltier) and it uses both a primary and secondary y axis. The data requires that I manually set the max range on the chart, but, it's set up to select different data ranges that vary quite a bit. It would be ideal if I could set the max range to automatic on the primary range and have the secondary max track to the primary.
Any ideas?
thanks
alan
0
Comment
Question by:avoorheis
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 7

Expert Comment

by:TonShadow
ID: 34868619
Hi,

Could you attach a simple example with some data?

-T-
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34868627
add this to your codepage for the chart:

Private Sub Chart_Calculate()

    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = ActiveChart.Axes(xlValue).MaximumScale

End Sub


see attached and change dataseries values for the left axis, you'll see the max change becase the format for max is Auto, but the secondary will be tied to that.

Cheers,

Dave
secondary-tied-to-primary-max-ax.xlsm
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34868894
Is your chart in a worksheet or on its own sheet?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 41

Accepted Solution

by:
dlmille earned 400 total points
ID: 34869029
An update to my earlier post.

If the chart is in its own tab, the code works.  However, I updated it now to set axis based on tab selection.

And, just in case the chart is in your worksheet, I've added code to look for changes in the range "ChartRange", and the chart name is "Chart 1" (change that if there are more than 1 charts or the name has been changed, in the TrackPrimary() routine), then it updates the secondary axis based on that...

Put this in a module:

Sub TrackPrimary()

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = ActiveChart.Axes(xlValue).MaximumScale

End Sub

Put this in your chart sheet if that's where your chart is:

Private Sub Chart_Activate()
    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = ActiveChart.Axes(xlValue).MaximumScale
End Sub


And put this in your sheet codepage if your chart is embedded in that sheet:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("ChartRange")) Is Nothing Then
        Call TrackPrimary
    End If
End Sub


That should cover the possibilities...

See attached tested demonstration workbook.

Dave
secondary-tied-to-primary-max-ax.xlsm
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 100 total points
ID: 34870141
Variation on Dave's theme: the attached uses the chart's calculate event for both charts.
secondary-tied-to-primary-max-ax.xlsm
0
 

Author Comment

by:avoorheis
ID: 34872612
I'll look at the code suggestions shortly.
To answer the questions, chart in on a worksheet with other data and charts.
I could post an example, but, I think it's a pretty straight forward question, no?
(have the secondary y axis track the max/min settings of the primary y axis, even if the corresponding data values are much different...chart source data is changed frequently)
0
 

Author Closing Comment

by:avoorheis
ID: 34872706
got a chance to review sooner than expected.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
conditional formatting 4 42
Turn several entries on single cell, into individual lines 14 27
And OR formula 5 22
If help 9 48
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,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

777 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