How to Remove Shapes from a Macro Free Workbook Using VBA

JohnGow383

Board Regular
Joined
Jul 6, 2021
Messages
141
Office Version
  1. 2013
Platform
  1. Windows
Hello,

I have a simple macro which loops through all worksheets in the workbook and then saves them in a chosen directory as seperate workbooks and then closes those after running. The files are saved as macro free workbooks. The macro is fired from a command button located in each sheet of the original workbook however, when saving them as macro free workbooks, the command button is also being saved in the new macrofree workbooks.
Does anyone know of a way for this not to save the command button? I guess I could do a loop to ensure the shapes are deleted prior to closing and saving the workbooks but this would be dangerous if I had other workbooks open i the background that contain command buttons. Here is my code for saving and closing.

VBA Code:
Sub SaveEachSheetsAsWB() 'Loop through each sheet and save as a seperate workbook

Dim ws As Worksheet
Dim wb As Workbook
Dim Path As String

Path = "P:\02 STORES\Send for Quotation\"

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each ws In ThisWorkbook.Worksheets
         Set wb = Workbooks.Add
         ws.Copy Before:=wb.Sheets(1)
         wb.SaveAs Filename:=Path & ws.Name & ".xlsx", FileFormat:=51
         Set wb = Nothing
         
Next ws

SaveCloseOtherWBs

Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox "All Stores Sheets are Saved as Seperate Workbooks located in " & vbNewLine & vbNewLine & "P:\02 STORES\Send for Quotation", vbOKOnly
         
End Sub

and for closing

VBA Code:
Sub SaveCloseOtherWBs() 'Saves and closes any other workbooks other than this workbook

Dim wb As Workbook
For Each wb In Workbooks
 If wb.Name <> ThisWorkbook.Name Then
   wb.Close savechanges:=True
 End If
Next wb
 
End Sub

Thanks in advance
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
You should be able to delete all shapes at once.
VBA Code:
    For Each ws In Sheets
        ws.DrawingObjects.Delete
    Next

If you want to loop through the shapes on each ws

Code:
    For Each ws In Sheets
        For Each shp In ws.Shapes
            shp.Delete
        Next shp
    Next
 
Upvote 0
You should be able to delete all shapes at once.
VBA Code:
    For Each ws In Sheets
        ws.DrawingObjects.Delete
    Next

If you want to loop through the shapes on each ws

Code:
    For Each ws In Sheets
        For Each shp In ws.Shapes
            shp.Delete
        Next shp
    Next
Thanks
My only concern is if I have another open workbook it would also delete the shapes there. Whilst I can add an exception line for a specific workbook (or ThisWorkbook.Worksheets - To avoid deleting the source command button) I think this could delete shapes where it's not supposed to.
 
Upvote 0
Yes, if you loop through each shape, you can check if the shape has a specific name, and don't delete if it does.
 
Upvote 0
Yes, if you loop through each shape, you can check if the shape has a specific name, and don't delete if it does.
In one spreadsheet I have open regularly there would be too many to list. It would be easier to only list the ones I wanted to delete. Dumb question, but how do I find out the codename for the shapes? It's a simple command button (which I assume will be commandbutton1) it's form control not activeX. How can I rename the shape to be more bespoke
 
Upvote 0
If you right click the shape, the name will show in the name box.

1657817375670.png


You can also loop through the shapes and not delete the shapes that are in a specific column or range or row.
 
Upvote 0
You should be able to delete all shapes at once.
VBA Code:
    For Each ws In Sheets
        ws.DrawingObjects.Delete
    Next

If you want to loop through the shapes on each ws

Code:
    For Each ws In Sheets
        For Each shp In ws.Shapes
            shp.Delete
        Next shp
    Next
I am unable to get this code to work. Even when I copy exactly. I'm not getting runtime errors but it's just not deleting the buttons. I also had a statement to only delete if it equals "Button 1" but it still isn't removing them. Any ideas?
 
Upvote 0
Lets see your code
Hi. This is called after the creation of the new workbooks from the sheets and before close all workbooks other than the one that the macro originated from

VBA Code:
Sub RemoveCmdButtons()

Dim shp As Shape, ws As Worksheet, wb As Workbook

For Each wb In Workbooks
  If wb.Name <> ThisWorkbook.Name Then
   For Each ws In Sheets
    For Each shp In ws.Shapes
     If shp.Name = "Button 1" Then
      shp.Delete
     End If
    Next shp
   Next ws
  End If
 Next wb
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,203
Members
452,617
Latest member
Narendra Babu D

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top