Home > Structure > Worksheets > Objects Excel Worksheet ObjectsHow to add objects, such as shapes and images, to a worksheet. If you copy data from a website, objects might also be copied. See how to list the objects on a sheet, or select and delete them. |
Macros - Delete All ShapesHere are two macros that will delete all of the shapes on an Excel worksheet. These macros can save you time, but use them with caution -- read the warning below! Warning: Be sure to make a backup copy of your Excel file before running either of these macros. You cannot undo the worksheet changes made by an Excel macro. Active Sheet - Delete All ShapesThis macro deletes all shapes on the active sheet only. It does not affect any other sheets in the workbook. Warning: Be sure to make a backup copy of your Excel file before running this macro. Sub DeleteShapesSheet() 'deletes all shapes on active sheet only Dim iSh As Long With ActiveSheet For iSh = .Shapes.Count To 1 Step -1 .Shapes(iSh).Delete Next End With End Sub All Sheets - Delete All ShapesThis macro deletes all shapes, on ALL sheets in the workbook. Warning: Be sure to make a backup copy of your Excel file before running this macro. Sub DeleteShapesAllSheets() Dim ws As Worksheet Dim iSh As Long For Each ws In ActiveWorkbook.Worksheets For iSh = ws.Shapes.Count To 1 Step -1 ws.Shapes(iSh).Delete Next Next ws MsgBox "Done" End Sub |
Change the Shape StyleTo make the shape look more like a button, you can add a Shape Style:
|
Bevelled Theme SettingIf you plan to make several shapes in the workbook, and want them all to have a bevelled effect, you can change one of the Theme settings.
Now, when you look at the Style Gallery, the bottom row shapes will have a bevelled effect, like the styles had in Excel 2007 and 2010. |
Add Text to ButtonTo make it clear what the button does, add some text. In this example, the button will run a macro that toggles the column headings, from numbers, to letters, or letters to numbers. To add text:
|
Make the Button Run a MacroTo make the macro run a macro that has been stored in the workbook:
NOTE: To select a shape after a macro has been assigned, right-click on the shape. |
Get the Sample FileGet the sample file for the Macro Button tutorial. The zipped file is in xlsm format, and contains macros. More Tutorials |
Last updated: September 15, 2023 10:35 AM