PDA

View Full Version : Select and Edit certain type of ChartObject in VBA



coes
07-23-2019, 03:12 AM
Dear All,

Appreciate if someone can help me on this code,
Basically I want to edit the axis value in certain type of Chart
But this code didn't worked.

Dim objCht As ChartObject

For Each objCht In ActiveSheet.ChartObjects

If ActiveChart.ChartType = xlBarStacked Then

With objCht.Chart
With .Axes(xlValue)
.MaximumScale = ActiveSheet.Range("M8").Value
.MinimumScale = ActiveSheet.Range("M9").Value
End With
End With

Else

With objCht.Chart
With .Axes(xlCategory)
.MaximumScale = ActiveSheet.Range("M8").Value
.MinimumScale = ActiveSheet.Range("M9").Value
End With
End With

End If

Next objCht

Thanks in advance

p45cal
07-23-2019, 04:46 AM
Change the only instance of ActiveChart to objCht.Chart

Aside from that you could, if you wanted, shorten the code a bit:
Dim objCht As ChartObject

For Each objCht In ActiveSheet.ChartObjects
With objCht.Chart
If .ChartType = xlBarStacked Then myAxis = xlValue Else myAxis = xlCategory
With .Axes(myAxis)
.MaximumScale = ActiveSheet.Range("M8").Value
.MinimumScale = ActiveSheet.Range("M9").Value
End With
End With
Next objCht

dangelor
07-23-2019, 04:54 AM
It might help if you explained what in your code didn't work as expected.

coes
07-24-2019, 08:33 AM
Hello p45cal

It works perfectly :thumb:thumb

Many Thanks for your help