[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

secondary axis maximum to track primary axis

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
avoorheis
Asked:
avoorheis
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
TonShadowCommented:
Hi,

Could you attach a simple example with some data?

-T-
0
 
dlmilleCommented:
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
 
Rory ArchibaldCommented:
Is your chart in a worksheet or on its own sheet?
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
dlmilleCommented:
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
 
Rory ArchibaldCommented:
Variation on Dave's theme: the attached uses the chart's calculate event for both charts.
secondary-tied-to-primary-max-ax.xlsm
0
 
avoorheisAuthor Commented:
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
 
avoorheisAuthor Commented:
got a chance to review sooner than expected.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now