VBA: How can I run a Y-Axis only Min/Max Scale Macro on a Single Line Chart?

Question

I am a noob at coding but have been trying to embed a macro that dynamically scales the Y-Axis of a chart in an excel worksheet based on max / min figures generated within set cells. (I also want the macro to trigger when the contents of a specific cell are changed but that's the next bit). Also, the code below is set to run the scaling macro on two charts so I was going to have a dummy chart in the sheet as I cannot figure out how to put it to one chart but if this can be avoided, great.

I have taken two separate codes from this site and applied them to no success. One macro ran the max / min scale but it did it on all the charts within the workbook which I don't want, I just want it to run on 1 chart in a sheet. I then found another piece of code that looked like it might work but that also changed the X-axis scale to be the same as the Y-axis I was aiming for.

Sub ScaleAxes()

    Dim Sht As Worksheet
    Dim ws As Worksheet
    Dim chtObj As ChartObject
    Dim ChtNames

    Set ws = Worksheets("Sheet1")
    ' you need to get the names of the charts into an array, not ChartObjects array
    ChtNames = Array("ChartName1", "ChartName2")

    ' first loop through all worksheet
    For Each Sht In ActiveWorkbook.Worksheets
        ' loop through all ChartObjects in each worksheet
        For Each chtObj In Sht.ChartObjects
            With chtObj

                '=== use the Match function to check if current chart's name is found within the ChtNames array ===
                If Not IsError(Application.Match(.Name, ChtNames, 0)) Then
                    With .Chart.Axes(xlCategory, x1Primary)
                        .MaximumScale = ws.Range("M9").Value
                        .MinimumScale = ws.Range("M10").Value
                        .MajorUnit = ws.Range("M11").Value
                    End With
                End If
            End With
        Next chtObj
    Next Sht

End Sub

The error is that it scales the X-axis and Y-axis the same but I just want to scale the Y-axis to my set Max and Min cells.

You really have two problems in your code in the With .Chart.Axes(xlCategory, x1Primary) statement: a) change xlCategory to xlValue to change the Y-axis scale, and b) your xlPrimary is mis-spelled, using the number one "1" instead of a lower case L "l". — PeterT

simple-solution simple-solution · Accepted Answer · 2019-09-09T17:42:55

I recorded the following:

Sub Chart_Max_Y()
'
' Chart_Max_Y Macro
'
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = 2
    ActiveChart.Axes(xlValue).MaximumScale = 4
    Application.CommandBars("Format Object").Visible = False
End Sub

Transferring this to your macro should result in:

                [...]
                With .Chart.Axes(xlValue)
                    .MaximumScale = ws.Range("M9").Value
                    .MinimumScale = ws.Range("M10").Value
                    .MajorUnit = ws.Range("M11").Value
                End With
                [...]

VBA: How can I run a Y-Axis only Min/Max Scale Macro on a Single Line Chart?

1 Answers