SOLVED

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

Iron Contributor

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
1 Reply
best response confirmed by hrh_dash (Iron Contributor)
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

 

1 best response

Accepted Solutions
best response confirmed by hrh_dash (Iron Contributor)
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

 

View solution in original post