1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro for Formatting Charts

Discussion in 'VBA Macros' started by Colin Mannion, May 11, 2017.

  1. Colin Mannion

    Colin Mannion Member

    Messages:
    52
    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

    Attached Files:

  2. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi Colin,

    Try the following:
    Code (vb):
    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
    Thomas Kuriakose likes this.
  3. Colin Mannion

    Colin Mannion Member

    Messages:
    52
    Hi PCosta

    Thanks for this.

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

    Colin
  4. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    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 likes this.
  5. Colin Mannion

    Colin Mannion Member

    Messages:
    52
    Aha!!

    Appreciate that PCosta :)

    Colin
  6. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    You are welcome :)
  7. Colin Mannion

    Colin Mannion Member

    Messages:
    52
    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:

    Code (vb):

        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

    Attached Files:

  8. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    Try using the following:
    Code (vb):
    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:
    Code (vb):
    ActiveChart.Axes(xlValue).MajorGridlines.Delete
  9. Colin Mannion

    Colin Mannion Member

    Messages:
    52
    Thanks for that PCosta - working a treat!

    Colin
  10. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Don't mention it :)
  11. Debaser

    Debaser Active Member

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

    Code (vb):

    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
     
    Thomas Kuriakose and PCosta87 like this.
  12. Colin Mannion

    Colin Mannion Member

    Messages:
    52
    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

    Attached Files:

  13. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,028
    Hi ,

    See if this is acceptable.

    Narayan

    Attached Files:

  14. Colin Mannion

    Colin Mannion Member

    Messages:
    52
    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

    Attached Files:

  15. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,028
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

    Colin Mannion likes this.
  16. Colin Mannion

    Colin Mannion Member

    Messages:
    52
    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

    Attached Files:

Share This Page