I inadvertently created a bunch of buttons on a sheet. I first tried
manually deleting them one by one but there are hundreds of them. I
found this code and ammended it to delete each button one by one, but
I keep running into a runtime error because I've deleted some of the
buttons out of sequence and it looks to actually run each line whether
the button number is there or not.

Is there a way that I can delete all buttons on a page regardless of
the number?

Sub DeleteButtons()
Application.DisplayAlerts = True
ActiveSheet.Unprotect
ActiveSheet.Shapes("Button 595").Select
Selection.Cut
' *** I ommitted the code for buttons 594 thru 2 so that I could post
here, but it's the same***
ActiveSheet.Shapes("Button 1").Select
Selection.Cut
Application.DisplayAlerts = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFiltering:=True
End Sub

Try this code to delete all the Buttons, and only the Buttons, (from the
Form Toolbar) instead...

Sub DeleteButtons()
Dim S As Shape
ActiveSheet.Unprotect
On Error GoTo SkipIt
For Each S In ActiveSheet.Shapes
If S.FormControlType = xlButtonControl Then S.Delete
NextShape:
Next
On Error GoTo 0
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFiltering:=True
Exit Sub
SkipIt:
Err.Clear
Resume NextShape
End Sub

Note 1: Notice that with this code, you do not have to list each button
individually.

Note 2: I left the DisplayAlerts statements out as I didn't see why you
needed them and I used the exact Protection statements as you posted them
(assuming they were correct for your setup).

--
Rick (MVP - Excel)


"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:a109e0de-d133-4390-ae6c-(E-Mail Removed)...
>I inadvertently created a bunch of buttons on a sheet. I first tried
> manually deleting them one by one but there are hundreds of them. I
> found this code and ammended it to delete each button one by one, but
> I keep running into a runtime error because I've deleted some of the
> buttons out of sequence and it looks to actually run each line whether
> the button number is there or not.
>
> Is there a way that I can delete all buttons on a page regardless of
> the number?
>
> Sub DeleteButtons()
> Application.DisplayAlerts = True
> ActiveSheet.Unprotect
> ActiveSheet.Shapes("Button 595").Select
> Selection.Cut
> ' *** I ommitted the code for buttons 594 thru 2 so that I could post
> here, but it's the same***
> ActiveSheet.Shapes("Button 1").Select
> Selection.Cut
> Application.DisplayAlerts = True
> ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
> Scenarios:=True _
> , AllowFiltering:=True
> End Sub

This may work if the number of buttons isn't too large (whatever that means).

activesheet.buttons.delete



"J.W. Aldridge" wrote:
>
> I inadvertently created a bunch of buttons on a sheet. I first tried
> manually deleting them one by one but there are hundreds of them. I
> found this code and ammended it to delete each button one by one, but
> I keep running into a runtime error because I've deleted some of the
> buttons out of sequence and it looks to actually run each line whether
> the button number is there or not.
>
> Is there a way that I can delete all buttons on a page regardless of
> the number?
>
> Sub DeleteButtons()
> Application.DisplayAlerts = True
> ActiveSheet.Unprotect
> ActiveSheet.Shapes("Button 595").Select
> Selection.Cut
> ' *** I ommitted the code for buttons 594 thru 2 so that I could post
> here, but it's the same***
> ActiveSheet.Shapes("Button 1").Select
> Selection.Cut
> Application.DisplayAlerts = True
> ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
> Scenarios:=True _
> , AllowFiltering:=True
> End Sub

--

Dave Peterson

thanx!

Worked purfectly!

hit F5, GOTO , then select Special, then select Objects and OK
this returns to the active sheet with ALL objects selected. you could hit
Delete now and remove the all or TAB to check that you're got the buttons.


Sub DeleteObjects()
ActiveSheet.DrawingObjects.Delete
End Sub

"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:a109e0de-d133-4390-ae6c-(E-Mail Removed)...
> I inadvertently created a bunch of buttons on a sheet. I first tried
> manually deleting them one by one but there are hundreds of them. I
> found this code and ammended it to delete each button one by one, but
> I keep running into a runtime error because I've deleted some of the
> buttons out of sequence and it looks to actually run each line whether
> the button number is there or not.
>
> Is there a way that I can delete all buttons on a page regardless of
> the number?
>
> Sub DeleteButtons()
> Application.DisplayAlerts = True
> ActiveSheet.Unprotect
> ActiveSheet.Shapes("Button 595").Select
> Selection.Cut
> ' *** I ommitted the code for buttons 594 thru 2 so that I could post
> here, but it's the same***
> ActiveSheet.Shapes("Button 1").Select
> Selection.Cut
> Application.DisplayAlerts = True
> ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
> Scenarios:=True _
> , AllowFiltering:=True
> End Sub