Excel Macro: Deleting All Textboxes Without Looping

I’ve described a Excel VBA macro that deletes all text-boxes from an active sheet.
Actually, we can remove all text-boxes without looping.
This is the macro.

Macro Examples

Sub DeleteAllTextboxes_WithoutLooping()

  ActiveSheet.TextBoxes.Delete

End Sub

Description

We need only the following code:

  ActiveSheet.TextBoxes.Delete

I will explain about this simple code.

The Activesheet property returns an active Worksheet object or Chart object.
obj-Excel-ActiveSheet
The ActiveSheet property is a member of the Excel.Global class, so we can omit the Application property.

And the Worksheet.TextBoxes method returns a TextBoxes collection object that represents all the text-boxes on a worksheet.
obj-Excel-TextBoxes

The Worksheet.TextBoxes method and TextBoxes object are hidden members.

If you would like to toggle the display of hidden members, right-click the Object Browser and click Show Hidden Members on the shortcut menu. Then hidden members appear in gray text.

The TextBoxes.Delete method deletes the text-boxes.
obj-Excel-TextBoxes-Delete

  ActiveSheet.TextBoxes.Delete

That’s all.

Properties

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

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007