• Using VBA to set primary and secondary y-axis values

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » Using VBA to set primary and secondary y-axis values

    Author
    Topic
    #478831

    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/showthread//23749-Macro-to-adjust-scale-of-Chart-Axis-(Encligh-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

    Viewing 4 reply threads
    Author
    Replies
    • #1296781

      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?

    • #1296785

      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

    • #1296792

      It’s not ActiveChart.Axes(xlSecondary) but ActiveChart.Axes(xlvalue, xlSecondary)

    • #1296794

      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.

    • #1296797

      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.

    Viewing 4 reply threads
    Reply To: Using VBA to set primary and secondary y-axis values

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: