To work around this behavior, use Microsoft Visual Basic for Applications (VBA) code to format the shapes.
Note In Visual Basic Editor, click
Object Browser on the
View menu to find the correct objects, methods, and properties that are used in VBA code.
The following example illustrates how to use VBA code to format chart elements:
- Start Excel 2007.
- Add the following data to Sheet1:
A1 A
A2 2
B1 B
B2 2
- Select A1: B2.
- On the Insert tab, click Line in the Charts group, and then click Line. Notice that the chart is displayed in Sheet1.
- On the Developer tab, click Visual Basic in the Code group to start Visual Basic Editor.
- Add the following code example to format chart elements.
Sub ProgramChartObjects()
Dim cht
Set cht = ActiveSheet.ChartObjects(1)
cht.Activate
ActiveChart.ApplyLayout (10)
cht.ShapeRange.Height = 200
cht.ShapeRange.Item(1).Glow.Color.SchemeColor = 8
cht.ShapeRange.Item(1).Glow.Radius = 20 'Max is 20
cht.ShapeRange.Item(1).Glow.Color.RGB = RGB(255, 0, 0)
cht.ShapeRange.Item(1).Glow.Color.TintAndShade = 0.5
cht.ShapeRange.Item(1).Shadow.Style = msoShadowStyleInnerShadow
cht.ShapeRange.Item(1).Shadow.Visible = True
cht.ShapeRange.Item(1).Shadow.Blur = 21
cht.ShapeRange.Item(1).Shadow.ForeColor.RGB = RGB(0, 255, 0)
cht.ShapeRange.Item(1).Shadow.Transparency = 0.45
'Stop
End Sub
- Press F5. Notice that the chart in Sheet1 is formatted.
o see all the declared variables and values in the current procedure, follow these steps:
- In Visual Basic Editor, click Locals Window on the View menu.
- Uncomment "Stop" in the code example.
- Press F5.