+ Reply to Thread
Results 1 to 4 of 4

secondary axis scale

  1. #1
    Kent Smith
    Guest

    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



  2. #2
    John Mansfield
    Guest

    RE: secondary axis scale

    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
    >
    >
    >


  3. #3
    Kent Smith
    Guest

    Re: secondary axis scale

    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" <JohnMansfield@discussions.microsoft.com> wrote in message
    news:20A66C5F-9948-4987-AC8B-883B3B413D00@microsoft.com...
    > 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
    >>
    >>
    >>




  4. #4
    John Mansfield
    Guest

    Re: secondary axis scale

    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" <JohnMansfield@discussions.microsoft.com> wrote in message
    > news:20A66C5F-9948-4987-AC8B-883B3B413D00@microsoft.com...
    > > 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
    > >>
    > >>
    > >>

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1