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. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 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.

  4. #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

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

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

    RobinHackshall (2011-09-07)

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

  8. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 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
  •