How to Remove Shapes from a Macro Free Workbook Using VBA

JohnGow383

Board Regular
Joined
Jul 6, 2021
Messages
133
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 find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

JohnGow383

Board Regular
Joined
Jul 6, 2021
Messages
133
Office Version
  1. 2013
Platform
  1. Windows
The final solution to remove command buttons when saving multiple sheets as seperate macro free workbooks of same name as their sheets is as follows:

VBA Code:
Sub RemoveCmdButtons()

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

For Each wb In Workbooks    'Loop through each open workbook
      If wb.Name <> ThisWorkbook.Name Then  'Except the source workbook with the macro - Ensures the source file isn't affected
   For Each ws In wb.Sheets  'Loop through each sheet in each workbook
        For Each shp In ws.Shapes  'Loop through each shape in each sheet of each workbook
           If shp.Name = "Name of Button Here" Then  'Added to specify one particular button - Rename the button so it's unique to avoid unwanted generic button names being removed in other open WBs
               shp.Delete  'Delete the button
                  End If
            Next shp
        Next ws
      End If
   Next wb
End Sub
 
Solution

Forum statistics

Threads
1,176,199
Messages
5,901,866
Members
434,926
Latest member
will_maunders

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
Top