Excel Macro: Aligning Texts to the Center in Shapes on an Active Worksheet

This Excel VBA macro aligns text to the center in all shapes on an active worksheet.

Macro Example

Sub AlignToCenterInShape_ActiveSheet()

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes

    With shp.TextFrame
      .HorizontalAlignment = xlHAlignCenter
      .VerticalAlignment = xlVAlignCenter
    End With

  Next shp

End Sub

Description

Variable Declaration

Sub...

  Dim shp As Shape

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

obj-Excel-Shape

Starts Looping

Sub...

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes

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

The code is ActiveSheet.Shapes but this means the Worksheet.Shapes object.
I’ll explain about this.

[Application.]ActiveSheet

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

obj-Excel-Application-ActiveSheet

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

obj-Excel-Global-ActiveSheet

Worksheet.Shapes property

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

obj-Excel-Worksheet-Shapes

So the code: ActiveSheet.Shapes means the Worksheet.Shapes object.

Starts the With structure

Sub...

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes

    With shp.TextFrame

The 7th lines starts the With…End With statement.

This statement tells Excel that any action apply to the Shape.TextFrame object.

Horizontal Alignment

Sub...

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes

    With shp.TextFrame
      .HorizontalAlignment = xlHAlignCenter

The 8th line aligns the text to the horizontal center in a shape.

TextFrame HorizontalAlignment property

The TextFrame.HorizontalAlignment property returns or sets a XlHAlign value that represents the horizontal alignment.

obj-Excel-TextFrame-HorizontalAlignment

By setting the TextFrame.HorizontalAlginment property to xlHAlignCenter, the horizontal alignment is set to the center.

Vertical Alignment

Sub...

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes

    With shp.TextFrame
      .HorizontalAlignment = xlHAlignCenter
      .VerticalAlignment = xlVAlignCenter

The 9th line aligns the text to the vertical center in a shape.

TextFrame.VerticalAlignment

The TextFrame.VerticalAlginment property returns or sets a XlVAlign value that represents the vertical alignment.

obj-Excel-TextFrame-VerticalAlignment

By setting the TextFrame.VerticalAlginment property to xlVAlignCenter, the vertical alignment is set to the center.

End With and Loops Back

Sub...

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes

    With shp.TextFrame
      .HorizontalAlignment = xlHAlignCenter
      .VerticalAlignment = xlVAlignCenter
    End With

  Next shp

The 10th line ends the With…End With statement for the Shape.TextFrame object.
And the 12th line loops back to evaluate the next Shape object.

After every shape is evaluated, this macro ends.

Sub AlignToCenterInShape_ActiveSheet()

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes

    With shp.TextFrame
      .HorizontalAlignment = xlHAlignCenter
      .VerticalAlignment = xlVAlignCenter
    End With

  Next shp

End Sub

Properties

http://www.relief.jp/itnote/archives/excel-macro-centering-text-shapes.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007