CommandButton does not delete

[BebeM]

Hello All, please assist us, the below macro does not delete the command button, where did we go wrong? thank you so much.

Private Sub Copy_save()

Dim saveDate As Date
    Dim saveTime As Variant
    Dim formatDate As String
    Dim backupFolder As String
    Dim Sh As shapes
   
    Const strFilePath   As String = "C:\D Drive\"

    saveDate = Date
    formatDate = Format(Now(), "mmmm yyyy")

 If MsgBox("You are now saying copy of this LineBooker Invoicing Workbook." & Chr(10) & _
                "Do you want to proceed?", 36, "Transport (Pty) Ltd") = vbYes Then
       
   Application.DisplayAlerts = False
        backupFolder = ThisWorkbook.path & "\"
        ActiveWorkbook.SaveCopyAs Filename:=backupFolder & Replace(ActiveWorkbook.Name, ".xlsm", "") & " " & formatDate & " " & formatTime & ".xlsm"
        Application.DisplayAlerts = True
       
        ActiveSheet.shapes("CommandButton1").Delete
        'Sheets(Db).shapes("CommandButton1").Delete
       
        MsgBox "You have successfully saved New Invoicing Workbook for LineBooker", 64, "Transport (Pty) Ltd"
       
       
    End If
   
End Sub

 

[RobP]

seems a little odd, as when I've created a "CommandButton1" from the activeX control for my sheet, and run a macro simply executing this line

ActiveSheet.Shapes("CommandButton1").Delete

it deletes it as expected. I guess need to be sure you are actually on the correct sheet (and CommandButton1 is actually called "CommandButton1") before trying to run that line. I assume thats the reason for your second (commented out) attempt). Maybe try Selecting the right sheet on the line prior to deleting ?

Rob

 

[RoryA]

If you are expecting the code to delete the button from the backup copy, it won't do that.

 

[BebeM]

If you are expecting the code to delete the button from the backup copy, it won't do that.

any suggestions RoryA?

 

[RoryA]

If that is what you are expecting (you haven't said), then you either need to delete the button, do the save, then reinstate the button, or save the copy, open it and delete the button, then save and close it.

 

[BebeM]

If that is what you are expecting (you haven't said), then you either need to delete the button, do the save, then reinstate the button, or save the copy, open it and delete the button, then save and close it.

...

 

[BebeM]

...

If that is what you are expecting (you haven't said), then you either need to delete the button, do the save, then reinstate the button, or save the copy, open it and delete the button, then save and close it.

Ok RoryA, I have replaced all words 'backupFolder' with wNewname and then, the CommandButton1 gets deleted on the main WB (which is not what we want), we want the CommandButton1 to delete on the saved WB. Your assistance will help a lot.

 

[RoryA]

My first question is whether you have a particular reason for using a commandbutton (i.e. activex) rather than a Form button? A form button would be much simpler to recreate. Alternatively, you could simply hide the button before saving (so the button would still be in the copy, but invisible) then unhide it again afterwards.

 

[BebeM]

My first question is whether you have a particular reason for using a commandbutton (i.e. activex) rather than a Form button? A form button would be much simpler to recreate. Alternatively, you could simply hide the button before saving (so the button would still be in the copy, but invisible) then unhide it again afterwards.

Hi RoryA,
The reason for the commandbutton is merely for eye attraction that will prompt others to click on it in order to generate a copy of the main WB and then close. So i keep failing to hide the commandbutton before saving and closing the main wb. Instead, i made copy of that Sheet with the commandbutton, hide it, on the second sheet I deleted the commandbutton, then run a code that will open the saved copy directly to the second sheet with no commandbutton.

Well at least it works for now, until you RoryA and the team provide us with another solution