secondary axis scale
 

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

----
Regards,
John Mansfield
http://www.pdbook.com


"Kent Smith" wrote:

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

Thanks John- I think the worksheet change event you describe is what I
need.
PS Your site looks like a good resource. -Kent Smith

"John Mansfield" wrote in message
...
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

----
Regards,
John Mansfield
http://www.pdbook.com


"Kent Smith" wrote:

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

Thanks Kent,

Here's some links to other good charting resources that you might want to
bookmark and refer to often:

http://www.peltiertech.com/
http://www.andypope.info/
http://www.tushar-mehta.com/
http://www.bmsltd.ie/Excel/Default.htm

----
Regards,
John Mansfield
http://www.pdbook.com

"Kent Smith" wrote:

Thanks John- I think the worksheet change event you describe is what I
need.
PS Your site looks like a good resource. -Kent Smith

"John Mansfield" wrote in message
...
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

----
Regards,
John Mansfield
http://www.pdbook.com


"Kent Smith" wrote:

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