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

0
votes

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

2
excelvbaexcel-charts
Take a look at this answer first How to avoid using Select in Excel VBA – Marcucciboy2
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
No need of VBA for this. Just define 1 formatted chart as a custom template and reuse it. support.office.com/en-us/article/… – Patrick Honorez

2 Answers

1
votes

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
0
votes

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