MS Excel: Generic macro to be applied to the selected chart

Question

I am trying to edit a piece of recorded macro code such that when I click on a chart and run the macro it will apply the formatting changes

I have tried "With ActiveChart.Parent" and "ActiveSheet.Shapes("NAM")"

Sub Macro2()
    ActiveSheet.Shapes("NAM").Fill.Visible = msoFalse
    ActiveChart.FullSeriesCollection(1).Select
    ActiveSheet.Shapes("NAM").Line.Visible = msoFalse
    ActiveChart.ChartGroups(1).GapWidth = 50
    ActiveChart.PlotArea.Select
    ActiveSheet.ChartObjects("NAM").Activate
    ActiveChart.FullSeriesCollection(1).Select
    ActiveChart.FullSeriesCollection(1).ApplyDataLabels
    ActiveChart.Axes(xlValue).Select
    Selection.TickLabelPosition = xlNone
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    Selection.Delete
End Sub

This would ideally remove the chart fill, chart border, gridlines, labels on the y-axis, and then add data labels

Take a look at this answer first [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Marcucciboy2, Aug 12 '19 at 12:46
You tell us what you want... But you don't specify how it's "not working". Please use the [edit] link below the question to provide that information *in* the question. — Cindy Meister, Aug 12 '19 at 13:55
No need of VBA for this. Just define 1 formatted chart as a custom template and reuse it. https://support.office.com/en-us/article/save-a-custom-chart-as-a-template-259a5f9d-a9ec-4b3f-94b6-9f5e55187f2a — iDevlop, Aug 12 '19 at 15:18

score 1 · Answer 1 · answered Aug 12 '19 at 14:42

An easy way to avoid using VBA to format your chart is to make the chart how you want it, and then save that as a named template. So when making future charts, you can apply this template and it format the chart to the template.

You can save it as a named template and call it from VBA. See below.

Sub CallChartTemplate()
    Range("A1:B27").Select 'Change to your range you are using for chart
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select 
    'Add type of chart you want it to be like [in my case, a clustered column chart]
    ActiveChart.SetSourceData Source:=Range("Sheet2!$L$1:$O$27") 'Setting source data of chart
    ActiveChart.ApplyChartTemplate () 'In parentheses, put location of where chart template is saved
    'Don't forget to add name of template as well
End Sub

Thanks! Was trying to make a nice one-click solution for the QA toolbar and this is perfect. — user2718435, Aug 12 '19 at 16:11

score 0 · Answer 2 · answered Aug 20 '19 at 17:11

Using a chart template is a good approach, if the chart isn't too complicated.

If you still want to use VBA, this does what you want:

Sub ChartCleanUp()
  With ActiveChart
    With .ChartArea
      .Format.Fill.Visible = False
      .Format.Line.Visible = False
    End With
    .ChartGroups(1).GapWidth = 50
    .SeriesCollection(1).HasDataLabels = True
    With .Axes(xlValue)
      .TickLabelPosition = xlNone
      .MajorGridlines.Delete
    End With
  End With
End Sub

MS Excel: Generic macro to be applied to the selected chart

2 Answers2