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.
↑ Back to the top
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.
↑ Back to the top
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
- 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.
↑ Back to the top
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.
↑ Back to the top