Excel Macro: Setting Shapes Properties to the Move and Size with Cells

This Excel VBA macro sets all shapes properties to the move and size with cells.

Macro Example

Sub SetMoveAndSizeWithCells()

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes
    shp.Placement = xlMoveAndSize
  Next shp

End Sub

Description

Variable Declaration

Sub...

  Dim shp As Shape

The 3rd line declares an object variable named shp that will refer to a Shape object.

Starts Looping

Sub...

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes

The 5th line starts to loop through all the elements (Shape object) of the Shapes collection.

[Application.]ActiveSheet

The [Application.]ActiveSheet property returns an active Worksheet or Chart object. So the Object Browser shows us that returned value is “Object”.

obj-Excel-Application-ActiveSheet

Because the ActiveSheet property is a member of the Excel.Global class, we can omit the Application property.

obj-Excel-Global-ActiveSheet

ActiveSheet.Shapes (Worksheet.Shapes)

The ActiveSheet.Shapes (Worksheet.Shapes property) returns a Shapes collection object that represents all the shapes on a specified worksheet.

obj-Excel-Worksheet-Shapes

Setting Shape.Placement Property

Sub...

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes
    shp.Placement = xlMoveAndSize

The 6th line sets the Shape.Placement property.

Shape.Placement

The Shape.Placement property returns or sets an XlPlacement value that represents the way the shape is attached to the cells below it.

obj-Excel-Shape-Placement

The XlPlacement enumeration specifies the way that the shape is attached to its underlying cells.

obj-Excel-XLPlacement-xlMoveAndSize

The xlMoveAndSize means the shape is moved and sized with the cells.

Loops Back

Sub...

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes
    shp.Placement = xlMoveAndSize
  Next shp

The 7th line loops back to get the next Shape object.

After every image is evaluated, this macro ends.

Sub SetMoveAndSizeWithCells()

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes
    shp.Placement = xlMoveAndSize
  Next shp

End Sub

Properties

http://www.relief.jp/itnote/archives/excel-macor-move-and-size-with-cells.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007