Jul 25 2022 11:45 PM
Is there a way to save xlsm in another workbook as xlsx and remove all user form controls?
The code below work perfectly but I would like to remove the user form controls when saved in xlsx file
Sub savefile()
Dim Fname As String
Dim Fpath As String
Dim ws As Worksheet
Set ws = Sheet1
Fname = ws.Range("B1")
Fpath = Environ("Userprofile") & "\Desktop\"
Worksheets("Sheet1").Copy
With ActiveWorkbook
.SaveAs FileName:=Fpath & Fname & " Billing Milestone", FileFormat:=xlOpenXMLWorkbook
.Close savechanges:=False
End With
MsgBox (Fname & " Billing Mileston has been saved to your desktop.")
End Sub
Jul 27 2022 02:42 AM
Solution@hrh_dash 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