1

I have a simple VBA code, which changes the minimum and maximum values of my chart according to values given in certain cells. The code looks like this:

Sub X_Axis_Values()

Dim Cht As Chart

Set Cht = ActiveSheet.ChartObjects("Chart 1").Chart

Cht.Axes(xlCategory, xlPrimary).MinimumScale = Range("E2").Value
Cht.Axes(xlCategory, xlPrimary).MaximumScale = Range("F2").Value

End Sub

The macro works fine for the primary axis (X axis in my case). I tried to extend the macro to define the minimum and maximum values for secondary axis (Y axis), but I'm constantly getting Run-time error "Method 'Axes' of object '_Chart' failed".

What's the reason, and how could I make it work? My current code is below.

Sub Axis_Values()

Dim Cht As Chart

Set Cht = ActiveSheet.ChartObjects("Chart 1").Chart

Cht.Axes(xlCategory, xlPrimary).MinimumScale = Range("E2").Value
Cht.Axes(xlCategory, xlPrimary).MaximumScale = Range("F2").Value
Cht.Axes(xlCategory, xlSecondary).MinimumScale = Range("G2").Value
Cht.Axes(xlCategory, xlSecondary).MaximumScale = Range("H2").Value

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Kolumbo
  • 51
  • 5

1 Answers1

1

I was able to figure it out myself.

The problem was wrong references of the axis's. I didn't know (until now), that the X-axis is referred as xlCategory and Y-axis as xlValue. With the following code the program is working fine:

Sub Axis_Max_Min_Values()

Dim Cht As Chart

Set Cht = ActiveSheet.ChartObjects("Chart 1").Chart

Cht.Axes(xlCategory).MaximumScale = Range("F2").Value
Cht.Axes(xlCategory).MinimumScale = Range("E2").Value
Cht.Axes(xlValue).MaximumScale = Range("H2").Value
Cht.Axes(xlValue).MinimumScale = Range("G2").Value


End Sub
Kolumbo
  • 51
  • 5
  • Is it possible to perform this same procedure without using "ActiveSheet."? Say, using Dim wsB As Object: Set wsB = wb1.Worksheets("test") etc. – Eric Jul 20 '23 at 17:48