Solved

secondary axis maximum to track primary axis

Posted on 2011-02-10
7
323 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 42

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
Independent Software Vendors: 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!

 
LVL 42

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

626 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