Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2011
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Using VBA to set primary and secondary y-axis values

    Hi,

    I am hoping that someone will be able to help me. I have no Visual Basic knowledge and am utilising the kindness and generosity of others to help solve my problems.

    Using a previous thread http://windowssecrets.com/forums/sho...ligh-Excel-XP) I have been able to create a Macro that set the primary y-axis values that I need for my chart. However, need to take this a step further.

    I have four charts within my spreadsheet and need the values to be applied to three of these four charts. Can this be done?

    In addition, I also need to be able to set the secondary y-axis values.

    Thank you in advance for any help/advice/guidance that you can offer.

    Robin

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,894
    Thanks
    0
    Thanked 84 Times in 80 Posts
    You can use ActiveChart.Axes(xlValue, xlSecondary) to refer to the secondary value axis. You can use a loop for the other part - how would the code determine which charts to manipulate and which to ignore?
    Regards,
    Rory
    Microsoft MVP - Excel.

  3. #3
    New Lounger
    Join Date
    Sep 2011
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Rory,

    Thanks for your help. I have added ActiveChart.Axes(xlSecondary), however, this has resulted in changing both the primary and secondary scales to the secondary values that I have set. Are you able to advise further? I have included my code below.

    Option Explicit
    Sub newScale()
    If ActiveChart Is Nothing Then
    MsgBox "Please select the chart to modify first."
    Exit Sub
    End If
    'With ActiveChart.Axes(xlCategory)
    '.MinimumScale = [xMin]
    '.CrossesAt = [xMin]
    '.MaximumScale = [xMax]
    'End With
    With ActiveChart.Axes(xlValue)
    .MinimumScale = [PriYMin]
    .CrossesAt = [PriYMin]
    .MaximumScale = [PriYMax]
    End With
    With ActiveChart.Axes(xlSecondary)
    .MinimumScale = [SecYMin]
    .CrossesAt = [SecYMin]
    .MaximumScale = [SecYMax]
    End With
    End Sub

    PriYMin = the minimum value I need on the primary y-axis
    PriYMax = the maximum value I need on the primary y-axis
    SecYMin = the minimum value I need on the secondary y-axis
    SecYMax = the maximum value I need on the secondary y-axis

    The above are set as named ranges.

    Thanks for your help.

    Robin

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,894
    Thanks
    0
    Thanked 84 Times in 80 Posts
    It's not ActiveChart.Axes(xlSecondary) but ActiveChart.Axes(xlvalue, xlSecondary)
    Regards,
    Rory
    Microsoft MVP - Excel.

  5. The Following User Says Thank You to rory For This Useful Post:

    RobinHackshall (2011-09-07)

  6. #5
    New Lounger
    Join Date
    Sep 2011
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts
    My mistake. The axis are now being updated as required.

    Not sure how loops work or how to select the three charts that need updating. I guess the easiest thing to do would be to record a Macro where I select each chart in turn and run the Marco to set the axis values. Such a Macro would need to be set to run when a specific cell changes value.

    Any further suggestions are appreciated.

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,894
    Thanks
    0
    Thanked 84 Times in 80 Posts
    If the charts have specific names, you could use those in a loop. You could use a Worksheet_Change macro to trigger your code assuming the changed cell is changed directly rather than as the result of a formula calculation.
    Regards,
    Rory
    Microsoft MVP - Excel.

Posting Permissions

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