Solved

secondary axis maximum to track primary axis

Posted on 2011-02-10
7
315 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 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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 Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

808 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