delete form controls in copied workbook

[wpryan]

Hi All, I have some code that copies a macro enabled workbook to an .xlsx format. The original file has some form controls on it, and I would like to include some code to delete the controls. The code to copy the workbook is below. What I am looking to do is to delete the form controls from the destination workbook (Destwb). How can I do it...?

Dim Sourcewb As Workbook
    Dim Destwb As Workbook

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With
    
    Dim answer As Integer
    
    answer = MsgBox("File will be saved in .xlsx format in original file location." & vbCr & vbCr & _
    "Email will be prepared. Please attach any supplemental information", vbOKCancel + vbInformation, "File Save Location")
    
    If answer = vbCancel Then
        Exit Sub
    Else:
        Set Sourcewb = ActiveWorkbook
        Sheets.Copy 'Copy the ActiveSheet to a new workbook

        Set Destwb = ActiveWorkbook
        'Determine the Excel version and file extension/format
        With Destwb
            If Val(Application.Version) < 12 Then
                'You use Excel 97-2003
                FileExtStr = ".xls": FileFormatNum = -4143
            Else
                'You use Excel 2007-2016
                FileExtStr = ".xlsx": FileFormatNum = 51
            End If
        End With

 

[James006]

Hi,
Once you have done your copy, you can test following macro:

Sub Remove_All_Objects()
    On Error Resume Next
    ActiveSheet.DrawingObjects.Visible = True
    ActiveSheet.DrawingObjects.Delete
    On Error GoTo 0
End Sub

 

[wpryan]

Hi,
Once you have done your copy, you can test following macro:

Sub Remove_All_Objects()
    On Error Resume Next
    ActiveSheet.DrawingObjects.Visible = True
    ActiveSheet.DrawingObjects.Delete
    On Error GoTo 0
End Sub

Hi there, thanks for your reply. It's not working - completely ignored by the code...

 

[James006]

What do you mean by " completely ignored by the code... " ???

Just tested macro , and it is working fine : deleting all Form Controls ...

 

[wpryan]

What do you mean by " completely ignored by the code... " ???

Just tested macro , and it is working fine : deleting all Form Controls ...

 

[wpryan]

I presume it was "completely ignored by the code" is because of the "on error resume next" line... When I removed it I found "Error -2147024809: The specified value is out of range." The following modification to my code fixed the problem.

With Destwb
            .Sheets("Instructions").Select
            .ActiveSheet.Buttons("btn_ClearData").Delete
            .ActiveSheet.Buttons("btn_ManData").Delete
            .ActiveSheet.Buttons("btn_ImportImage").Delete
            .ActiveSheet.Buttons("btn_ImportData").Delete
            .ActiveSheet.Buttons("btn_Finalize").Delete
            ...

 

[James006]

Glad you could fix your problem