How to Remove Shapes from a Macro Free Workbook Using VBA

[JohnGow383]

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.

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

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

 

[davesexcel]

You should be able to delete all shapes at once.

    For Each ws In Sheets
        ws.DrawingObjects.Delete
    Next

If you want to loop through the shapes on each ws

    For Each ws In Sheets
        For Each shp In ws.Shapes
            shp.Delete
        Next shp
    Next

 

[JohnGow383]

You should be able to delete all shapes at once.

    For Each ws In Sheets
        ws.DrawingObjects.Delete
    Next

If you want to loop through the shapes on each ws

    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.

 

[davesexcel]

Yes, if you loop through each shape, you can check if the shape has a specific name, and don't delete if it does.

 

[JohnGow383]

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

 

[davesexcel]

If you right click the shape, the name will show in the name box.

You can also loop through the shapes and not delete the shapes that are in a specific column or range or row.

 

[JohnGow383]

You should be able to delete all shapes at once.

    For Each ws In Sheets
        ws.DrawingObjects.Delete
    Next

If you want to loop through the shapes on each ws

    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?

 

[davesexcel]

Lets see your code

 

[JohnGow383]

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

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

 

[davesexcel]

I assume you need wb.sheets.