Macro for Formatting Charts

[Colin Mannion]

HELLO

I have a large number of charts where i need to update the format e.g. remove borders, change fonts etc.

Have tried to put together a quick macro based on something I found online but it hasn't worked - see file attached - you can see I am a novice at this.

Sub Update_Chart()

Dim cht As ChartObject
Dim sht As Worksheet

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each cht In sht.ChartObjects

cht.Activate
Active.Chart.Line.Visible = msoFalse

Next cht

CurrentSheet.Activate
Application.EnableEvents = True

End Sub

If anyone has a quick solution for this, it would be appreciated

Thanks in advance.
Colin

 

[PCosta87]

Hi Colin,

Try the following:

Sub Update_Chart()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
       
    Dim cht As ChartObject
   
    For Each cht In ActiveSheet.ChartObjects
        ActiveSheet.Shapes(cht.Name).Line.Visible = msoFalse
    Next cht
   
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Hope this helps

 

[Colin Mannion]

Hi PCosta

Thanks for this.

It seems to do it for the first chart and then stop.

Colin

 

[PCosta87]

Oh yes, I forgot
You have all charts with the same name... make sure all charts have a different name and you are good to go.
If they are created one after the other Excel automatically does it for you but, since you probably did copy/paste of Chart 1, it seems they all ended up with the same name.

 

[Colin Mannion]

Aha!!

Appreciate that PCosta

Colin

 

[PCosta87]

You are welcome

 

[Colin Mannion]

HI PCosta

Hope everything is okay with you.

I have tried to build on that macro you kindly put together for me, so I can change several chart attributes at the same time. I recorded a macro with the actions required (removing gridlines and changing axes fonts) and inserted them into the middle section of the code you wrote, andf it is coming up with a debug error. Code is below - file is attached:

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim cht As ChartObject

    For Each cht In ActiveSheet.ChartObjects

        ActiveSheet.Shapes(cht.Name).Line.Visible = msoFalse
            ActiveChart.PlotArea.Select
            ActiveChart.Axes(xlValue).Select
            ActiveChart.Axes(xlValue).MajorGridlines.Select
            ActiveChart.Axes(xlValue).Select
            Selection.Format.Line.Visible = msoFalse
            ActiveChart.Axes(xlCategory).Select
        With Selection.Format.TextFrame2.TextRange.Font.Fill
              .Visible = msoTrue
              .ForeColor.ObjectThemeColor = msoThemeColorBackground1
              .ForeColor.TintAndShade = 0
              .ForeColor.Brightness = -0.5
              .Transparency = 0
              .Solid
        End With
        With Selection.Format.TextFrame2.TextRange.Font.Fill
              .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorBackground1
              .ForeColor.TintAndShade = 0
              .ForeColor.Brightness = -0.5
              .Transparency = 0
            .Solid
        End With
    Application.CommandBars("Format Object").Visible = False

    Next cht

    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

  .Transparency = 0
  .Solid
  End With
  Application.CommandBars("Format Object").Visible = False

  Next cht

  Application.EnableEvents = True
  Application.ScreenUpdating = True

End Sub

Thanks in advance.
Colin

 

[PCosta87]

Hi,

Try using the following:

Sub Update_Chart()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
     
    Dim cht As ChartObject
 
    For Each cht In ActiveSheet.ChartObjects
        ActiveSheet.Shapes(cht.Name).Line.Visible = msoFalse
        cht.Activate
        ActiveChart.Axes(xlValue).MajorGridlines.Format.Line.Visible = msoFalse
        With ActiveChart.Axes(xlCategory).TickLabels.Font
            .Size = 10
            .Color = xlThemeColorDark1
'            .ColorIndex = 1
        End With
    Next cht
 
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

It hides the Grid Lines and applies some formatting to the font of the category axis, in this case I've set the size to 10 and the color to the one on your code.
You can either use Color or ColorIndex to change the color of the text.

If you wish to remove the Grid Lines completely, you can also use:

ActiveChart.Axes(xlValue).MajorGridlines.Delete

 

[Colin Mannion]

Thanks for that PCosta - working a treat!

Colin

 

[PCosta87]

Thanks for that PCosta - working a treat!

Colin

Don't mention it

 

[Debaser]

FWIW, you don't need to select anything or turn events on and off for this:

Sub Update_Chart()
    Dim cht                   As ChartObject

    Application.ScreenUpdating = False


    For Each cht In ActiveSheet.ChartObjects
        cht.ShapeRange.Line.Visible = msoFalse
        With cht.Chart
            .Axes(xlValue).MajorGridlines.Format.Line.Visible = msoFalse
            With .Axes(xlCategory).TickLabels.Font
                .Size = 10
                .Color = xlThemeColorDark1
                '            .ColorIndex = 1
            End With
        End With
    Next cht

    Application.ScreenUpdating = True

End Sub

 

[Colin Mannion]

Hi there

I have a sheet which has a lot of charts many of which have the same name :-(

Is there an easy way of renaming them to something unique so that macros will pick them up as different charts?

Thanks

 

[NARAYANK991]

Hi ,

See if this is acceptable.

Narayan

 

[Colin Mannion]

Hi Narayan

Thanks for this - the macro works great - I have now run into an other issue which I am hoping you may be able to help with.

The plan is to loop through the charts one by one to apply some formatting changes - changing axes, borders, gridline etc.

The macro I have coded for that generates an object error, which when cleared allows some of the steps to run. I can't quite work out what's going on - is there anything obvious to you?

Appreciate the help.
Colin

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Colin Mannion]

Hi Narayan

Thanks for this Apologies it has taken a few days to get back to you - this was pretty much there - I just had to tweak a couple of statements to look at the right axes and not delete the Axes themselves as that seemed to screw the whole thing up as I still wanted to keep the tick labels and it works great - nice one!!! Will save me hours!!

Really appreciate the help.
Colin