Solved

secondary axis maximum to track primary axis

Posted on 2011-02-10
7
305 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
Comment Utility
Hi,

Could you attach a simple example with some data?

-T-
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
Is your chart in a worksheet or on its own sheet?
0
Highfive Gives IT Their Time Back

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 41

Accepted Solution

by:
dlmille earned 400 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
got a chance to review sooner than expected.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 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

10 Experts available now in Live!

Get 1:1 Help Now