Delete All Buttons on a Sheet with VBA

[hobbes922]

Hello,

I have a macro that saves a sheet to another workbook. However, that sheet contains buttons with macros, so when saving to a new workbook, the macros get copied over.

Is there a VBA code to delete all of the buttons on a sheet? I can't name them individually, because for some reason, excel changes the name of the button between the sheet and the new workbook.

Is there a generic code to delete all buttons?

Thanks,
Deb

 

[jproffer]

Sub Clear_Buttons()
Dim i As Integer
    If ActiveSheet.ProtectContents = True Then
        MsgBox "The Current Workbook or the Worksheets which it contains are protected." & vbLf & "                          Please resolve these issues and try again."
    End If
    
On Error Resume Next
    For i = 1 To 10000
        ActiveWorkbook.sheets(i).Buttons.Delete
    Next i

Test on a COPY of your workbook, but I think this is what you need. I've found that even with 10,000 loops, it runs pretty quick.

 

[jproffer]

Sub Clear_Buttons()
Dim i As Integer
    If ActiveSheet.ProtectContents = True Then
        MsgBox "The Current Workbook or the Worksheets which it contains are protected." & vbLf & "                          Please resolve these issues and try again."
    End If
 
On Error Resume Next
    For i = 1 To 10000
        ActiveWorkbook.sheets(i).Buttons.Delete
    Next i
End Sub

Test on a COPY of your workbook, but I think this is what you need. I've found that even with 10,000 loops, it runs pretty quick.

It just occured to me that you said on a SHEET, not in a whole workbook. If that's the case, try this instead:

Sub Clear_Buttons()
Dim i As Integer
    If ActiveSheet.ProtectContents = True Then
        MsgBox "The Current Workbook or the Worksheets which it contains are protected." & vbLf & "                          Please resolve these issues and try again."
    End If
 
    On Error Resume Next
        ActiveSheet.Buttons.Delete
End Sub

 

[hobbes922]

This works great, thank you!