The Chart AreaVBA Name Chart Area

ChartArea is the container for all the other elements of a chart, including the plot area, axes, legend, series, data labels, and so on. The most common action performed with the chart area is modifying its format (border, fill color, and pattern) and the font settings for the entire chart, including chart and axis titles, legend, and data labels.

Take any existing chart that is loaded on a chart sheet. Turn on the macro recorder and use the Excel user interface to format the chart by right-clicking the chart area and selecting Format Chart Area. In the Patterns tab, borders section, select red color and the third of the four line weights. Click the Shadow check box so it is checked (has a tick mark). In the Area section, select a color (say, Light Turquoise). In the Font tab, select font size 14 and uncheck the Autoscale Font check box. The recorded code will look like this:

Sub Macro2()

ActiveChart.ChartArea.Select With Selection.Border .Colorlndex = 3 .Weight = xlMedium .LineStyle = xlContinuous End With

Selection.Shadow = True With Selection.Interior .Colorlndex = 34 .PatternColorlndex = 1 .Pattern = xlSolid End With

Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 14

.Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False

.Underline = xlUnderlineStyleNone .Colorlndex = xlAutomatic .Background = xlAutomatic End With End Sub

Note that the recorder has recorded all you did with the chart area (including many things you didn't even touch). The next step is to clean the code to limit its contents to your actions. I've reproduced the macro with redundant lines in bold and comments added at the appropriate places:

Sub Macro2()

ActiveChart.ChartArea.Select With Selection.Border

.Colorlndex = 3 'Red border

.Weight = xlMedium '3rd of the four line weights available '.LineStyle = xlContinuous (default)

End With

Selection.Shadow = True With Selection.Interior

.Colorlndex = 34 'Light turquoise fill '.PatternColorlndex = 1 (default) '.Pattern = xlSolid (default) End With

Selection.AutoScaleFont = False

0 0

Post a comment