Microsoft KB Archive/937620

= You cannot record new shapes, shape formatting, and shape effects by using the macro recorder in Excel 2007 =

Article ID: 937620

Article Last Modified on 6/4/2007

-

APPLIES TO


 * Microsoft Office Excel 2007

-



SYMPTOMS
In Microsoft Office Excel 2007, you cannot record new shapes, shape formatting, and shape effects by using the macro recorder. For example, you cannot record chart elements, fill-in colors, line styles, and line effects by using the macro recorder.

You can format labels and position titles that you can record by using the macro recorder in Excel 2007.

Note In Microsoft Office Excel 2003, you can record the formatting of all chart elements by using the macro recorder.



WORKAROUND
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.</li></ol>

o see all the declared variables and values in the current procedure, follow these steps:
 * 1) In Visual Basic Editor, click Locals Window on the View menu.
 * 2) Uncomment &quot;Stop&quot; in the code example.
 * 3) Press F5.

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

MORE INFORMATION
In Excel 2007, the new ChartFormat object provides access to OfficeArt formatting for chart elements. However, the properties of the ChartFormat object are read-only. Therefore, they cannot be recorded.

Additional query words: XL2007

Keywords: kbprb kbtshoot kbexpertisebeginner KB937620

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.