Excel Macro: Deleting All Textboxes from an Activesheet

In Microsoft Excel, we can remove all the shapes on an active sheet by using the Go to Special dialog-box.
But removing only the text-boxes is not easy. So I’ve made this Excel VBA macro.
This macro removes all the text-boxes from an active sheet.

Macro Example

Sub DeleteAllTextboxies()

  Dim i As Long

  With ActiveSheet.Shapes
    For i = .Count To 1 Step -1

      If .Item(i).Type = msoTextBox Then 
        .Item(i).Delete
      End If

    Next i
  End With

End Sub

Description

Variable Declaration

Sub...

  Dim i As Long

The 3rd line declares a long integer type variable named i.

Starts the With Structure

Sub...

  Dim i As Long

  With ActiveSheet.Shapes

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

This statement tells Excel that any action applies to the ActiveSheet.Shapes (Worksheet.Shapes object) .

[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

The ActiveSheet property is a member of the Excel.Global class, so 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 worksheet.

obj-Excel-Worksheet-Shapes

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

Starts Looping

Sub...

  Dim i As Long

  With ActiveSheet.Shapes
    For i = .Count To 1 Step -1

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

The number variable i starts at the max count of the shape (Shapes.Count) and is reduced by 1 on each iteration of the loop, ending after the value of variable reaches 1.

Shapes.Count

The Shapes.Count property returns a Long value that represents a number of the Shape object in the Shapes collection.

obj-Excel-Shapes-Count

Checks Shape Type

Sub...

  Dim i As Long

  With ActiveSheet.Shapes
    For i = .Count To 1 Step -1

      If .Item(i).Type = msoTextBox Then 

The 8th line checks whether the shape is a text-box.

The Shapes.Item method returns the single Shape object from the Shapes collection object.

obj-Excel-Shapes-Item

And the Shape.Type property returns or sets a MsoShapeType value that represents the shape type.

obj-Excel-Shape-Type

If it is a text-box, the Shape.Type property returns a const: msoTextBox.

obj-Office-MsoShapeType-msoTexBox

Deletes a Text-box

Sub...

  Dim i As Long

  With ActiveSheet.Shapes
    For i = .Count To 1 Step -1

      If .Item(i).Type = msoTextBox Then 
        .Item(i).Delete
      End If

If the shape is a text-box, the 9th line deletes it by the Shape.Delete method.

obj-Excel-Shape-Delete

Loops Back and End the With Structure

Sub...

  Dim i As Long

  With ActiveSheet.Shapes
    For i = .Count To 1 Step -1

      If .Item(i).Type = msoTextBox Then 
        .Item(i).Delete
      End If

    Next i
  End With

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

After every shape is evaluated, this macro ends.

Sub DeleteAllTextboxies()

  Dim i As Long

  With ActiveSheet.Shapes
    For i = .Count To 1 Step -1

      If .Item(i).Type = msoTextBox Then 
        .Item(i).Delete
      End If

    Next i
  End With

End Sub

Properties

http://www.relief.jp/itnote/archives/017880.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007