Macro to change chart name only once

A

Aligi

I have a sheet with 3 charts, two of them are called "Chart 2" and I need to
perform via macro a change on the axis.

If I do not rename one of them the macro performs only once. If I rename it
does it fine, but if I run the macro twice it get stuck.

That is what I did so far:


ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.ChartObjects("Chart 2").Name = "Chart 1"
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScaleIsAuto = True
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScaleIsAuto = True
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScaleIsAuto = True

Please help!
 
B

Barb Reinhardt

How about something like this

Sub Test()
Dim aWS As Worksheet
Dim ChtObj As ChartObject

Set aWS = ActiveSheet

For Each ChtObj In aWS.ChartObjects
Debug.Print ChtObj.Name, ChtObj.Chart.Axes(xlValue).MinimumScaleIsAuto
ChtObj.Chart.Axes(xlValue).MinimumScaleIsAuto = False
Debug.Print ChtObj.Name, ChtObj.Chart.Axes(xlValue).MinimumScaleIsAuto

Next ChtObj

End Sub

Here, I don't believe you'd need to change the chart name either.

Notice that I didn't use SELECT once in this code.
 
A

Aligi

Hi Barb,

Brilliat!! Did work perfect... mind the value was TRUE but I thought you
were testing if I am still awake :eek:)

Thank you very much.
 
D

Dave Peterson

Maybe you could refer to the charts by their numbers:

ActiveSheet.ChartObjects(1).Activate

Or rename the chartobjects before you start your macro and then don't rename
them in your code?

If you ctrl-click on the chart's border, you should see the chart name in the
namebox (to the left of the formula bar). Just type the new name (make it a
nice unique name!) and then hit enter to complete the rename.
 
B

Barb Reinhardt

I didn't intend to do that, but I guess you figured it out yourself.

Barb Reinhardt
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top