CODEWITHSUNDEEP
excelvbaexcel-charts
Sanny Wind
Sanny Wind

Reputation: 41

VBA excel. How to get access to chart scale by chart name?


It seems to be simple but can’t figure out how to operate wiht charts by name.
For example this code works fine:

Sub scale_chart()
    Dim chart_name As String
    chart_name = "Chart 3"
    ActiveSheet.ChartObjects(chart_name).Select
    With ActiveChart.Axes(xlValue)
        .MaximumScale = 800000
    End With
End Sub

But below codes provide "Run-time error '438' Object doesn't support proprty or method"

Sub scale_chart()
    Dim chart_name As String
    chart_name = "Chart 3"
    With ActiveSheet.Charts(chart_name).Axes(xlValue)
        .MaximumScale = 800000
    End With
End Sub

and

Sub scale_chart()
    Dim chart_name As String
    chart_name = "Chart 3"
    With ActiveSheet.ChartObjects(chart_name).Charts.Axes(xlValue)
        .MaximumScale = 800000
    End With
End Sub

I'm trying to avoid .select as it recomended here How to avoid using Select in Excel VBA but still need more knowledges.
Would be appreciated if someone point me what I'm doing wrong :)
p.s.: it's VBA Excel 2010

Upvotes: 0

Views: 272

Answers (1)

Dy.Lee
Dy.Lee

Reputation: 7567

Try,

Sub scale_chart()
    Dim chart_name As String
    Dim Ws As Worksheet
    Dim objCht As ChartObject
    Dim Cht As Chart
    
    Set Ws = ActiveSheet
    
    chart_name = "Chart 3"
    Set objCht = Ws.ChartObjects(chart_name)
    Set Cht = objCht.Chart
    With Cht.Axes(xlValue)
        .MaximumScale = 800000
    End With
End Sub

Upvotes: 0

Related Questions