CODEWITHSUNDEEP
excelvba
M Kaye
M Kaye

Reputation: 183

Set Chart Maximum and Minimum Values

I am trying to set a chart's maximum and minimum values using vba, referencing 2 cells. I am trying to refer to this chart which is already in the workbook. The following code:

Sub ScaleAxes()

    Dim Sht1 As Worksheet
    Dim Sht1Name As String
    Set Sht1 = Worksheets("Lists")

    With Application.ActiveChart.Axes(xlValue, xlPrimary)
        .MinimumScale = Sht1.Range("L2").Value
        .MaximumScale = Sht1.Range("L3").Value
    End With

End Sub

The code works if I have selected the chart, but how do I make it work if I am not clicked on the chart?

Thanks in advance,

Max

Upvotes: 0

Views: 1462

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

Use the code to define MyChtObj As ChartObject , later set MyChtObj to your chart's name in Sht1 (modify "Chart1 " to your chart's name).

Option Explicit

Sub ScaleAxes()

    Dim Sht1 As Worksheet
    Dim Sht1Name As String
    Set Sht1 = Worksheets("Lists")

    Dim MyChtObj As ChartObject
    Set MyChtObj = Sht1.ChartObjects("Chart 1") ' <-- modify "Chart 1" to your chart's name

    With MyChtObj.Chart.Axes(xlValue, xlPrimary)
        .MinimumScale = Sht1.Range("L2").Value
        .MaximumScale = Sht1.Range("L3").Value
    End With

End Sub

Upvotes: 2

Related Questions