Article ID: 931392
Article Last Modified on 10/15/2007
APPLIES TO
- Microsoft Office Excel 2007
SYMPTOMS
When you use a macro to add a shape to a workbook in Microsoft Office Excel 2007, the shape may not appear in the expected location.
CAUSE
This behavior occurs because shapes are positioned in relation to the current view. When you use a macro to add a shape to a workbook, the macro defines specific coordinates for the position of the shape. However, the coordinates that are provided by the macro provide different results when you use different views in Excel 2007.
WORKAROUND
To work around this behavior, replace the code in the macro by using code that resembles the following.
Sub <MacroName>()
Dim vw As XlWindowView
If ActiveWindow.View <> xlNormalView Then
vw = ActiveWindow.View
ActiveWindow.View = xlNormalView
End If
ActiveSheet.Shapes.AddShape 1, 800, 10, 100, 100
If vw <> xlNormalView Then
ActiveWindow.View = vw
End If
End Sub
Notes
- In this code sample, <MacroName> is the name of the macro.
- The code sample guarantees that the view is set to normal before the shape is added to the view. After the shape is added to the view, the view is reset to the view that was used before the macro was started.
MORE INFORMATION
Steps to reproduce the behavior
- Start Excel 2007, and then press ALT+F11 to start the Microsoft Visual Basic Editor.
On the Tools menu, click Macros, type TestShape, click Create, and then replace the code for the TestShape method with the following code.
Sub TestShape() Sheet1.Shapes.AddShape 1, 800, 10, 100, 100 End Sub- On the File menu, click Close and Return to Microsoft Excel.
- On the View tab, click View Macros, click TestShape, and then click Run.
Note The shape is displayed in column Q. - On the View tab, click Page Layout.
- In the View tab, click View Macros, click TestShape, and then click Run.
The shape is displayed in column P.
Keywords: kbtshoot kbprb kbexpertisebeginner KB931392
