Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all buttons on sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all buttons on sheet
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all buttons on sheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all buttons on sheet
thanx!
Worked purfectly! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all buttons on sheet
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete option buttons | Excel Discussion (Misc queries) | |||
Delete shapes, not buttons | Excel Programming | |||
Can't delete buttons | Excel Discussion (Misc queries) | |||
How to delete Radio buttons | Excel Programming | |||
Delete Buttons | Excel Discussion (Misc queries) |