secondary axis scale

  • Thread starter Thread starter Kent Smith
  • Start date Start date
K

Kent Smith

Hello-
I am plotting temperature data in a x-y scatter plot in Fahrenheit and am
using a dummy series to have a corresponding secondary axis in Celsius. I
would like the range of the secondary axis to change automatically when the
primary Minimumscale and/or Maximumscale values are changed. When I use the
"select" chart event, the macro runs before the new primary axis values are
entered. Is there a way I can reset the secondary axis values after the
primary values are changed?
Thanks in advance.. Kent Smith
 
Kent,

The macro below is triggered on the change event that targets a predefined
range as opposed to the more general select event. It will only run if any
of the data in cells A1:B10 is changed (assumes the data in cells A1:A10 is
on the primary series and the data in cells B1:B10 is on the secondary
series). The SetAxes macro that is called in the example below ties
worksheet cells to the maximum, minimum, and major units. If using a target
range instead of the select event works for you, then you might want to
change the reference to "Call SetAxes" to "Call YourMacro".

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
Call SetAxes
End If
End Sub

Private Sub SetAxes()
On Error Resume Next
Dim Cht As Chart
Set Cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart
With Cht.Axes(xlValue)
.MaximumScale = Range("C1")
.MinimumScale = Range("C2")
.MajorUnit = Range("C3")
End With
With Cht.Axes(xlValue, xlSecondary)
.MaximumScale = Range("D1")
.MinimumScale = Range("D2")
.MajorUnit = Range("D3")
End With
End Sub
 
Thanks John- I think the worksheet change event you describe is what I
need.
PS Your site looks like a good resource. -Kent Smith
 
Back
Top