Save xlsm in another workbook as xlsx and remove all user form controls

Question

Is there a way to save xlsm in another workbook as xlsx and remove all user form controls?&nbsp;The code below work perfectly but I would like to remove the user form controls when saved in xlsx fileSub savefile()

Dim Fname As String
Dim Fpath As String
Dim ws As Worksheet
   
   Set ws = Sheet1
  
   Fname = ws.Range("B1")
   Fpath = Environ("Userprofile") &amp; "\Desktop\"
   
Worksheets("Sheet1").Copy
    With ActiveWorkbook
       .SaveAs FileName:=Fpath &amp; Fname &amp; " Billing Milestone", FileFormat:=xlOpenXMLWorkbook
        .Close savechanges:=False
    End With
MsgBox (Fname &amp; " Billing Mileston has been saved to your desktop.")

End Sub

jkpieterse · Accepted Answer

hrh_dash&nbsp;Controls are shapes, so call this sub to remove them from the active worksheet:
Sub RemoveControls()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        If shp.Type = msoOLEControlObject Or shp.Type = msoFormControl Then
            shp.Delete
        End If
    Next
End Sub

&nbsp;

Forum Discussion

Save xlsm in another workbook as xlsx and remove all user form controls

1 Reply

Resources