Deleting a Button on a Worksheet

Hi

I have had trouble with some code to remove the macro button from a
worksheet (the spreadsheet is being reduced to bare values etc for
distribution as a final report etc)

By way of background, the workbook has been used by others so someone may
have copied or reinstated the macro button - see below.

I was using this code:
ActiveWorkbook.Sheets("Income & Expenditure A1").Shapes("Button 3").Delete
but I have recently got the runtime error "the item with the specified name
was not found"

I substituted this code and it seems to work OK
ActiveWorkbook.Sheets("Income & Expenditure A1").Shapes(1).Delete

My question is this: will this code ALWAYS delete the one macro button on
the sheet (there are no other "shapes").

Many thanks

Tim

You can use this to delete all shapes

Sub Shapes1()
'Delete all Objects except Comments
On Error Resume Next
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
On Error GoTo 0
End Sub


See also
http://www.rondebruin.nl/controlsobjectsworksheet.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tim Childs" <[email protected]> wrote in message news:[email protected]...
> Hi
>
> I have had trouble with some code to remove the macro button from a
> worksheet (the spreadsheet is being reduced to bare values etc for
> distribution as a final report etc)
>
> By way of background, the workbook has been used by others so someone may
> have copied or reinstated the macro button - see below.
>
> I was using this code:
> ActiveWorkbook.Sheets("Income & Expenditure A1").Shapes("Button 3").Delete
> but I have recently got the runtime error "the item with the specified name
> was not found"
>
> I substituted this code and it seems to work OK
> ActiveWorkbook.Sheets("Income & Expenditure A1").Shapes(1).Delete
>
> My question is this: will this code ALWAYS delete the one macro button on
> the sheet (there are no other "shapes").
>
> Many thanks
>
> Tim
>
>
>

Ron

That was really helpful - thanks

Given that I want to remove any buttons I used one of the tips on the link
to your site:
ActiveSheet.Buttons.Delete

vbw

Tim

"Ron de Bruin" <[email protected]> wrote in message
news:[email protected]...
> You can use this to delete all shapes
>
> Sub Shapes1()
> 'Delete all Objects except Comments
> On Error Resume Next
> ActiveSheet.DrawingObjects.Visible = True
> ActiveSheet.DrawingObjects.Delete
> On Error GoTo 0
> End Sub
>
>
> See also
> http://www.rondebruin.nl/controlsobjectsworksheet.htm
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Tim Childs" <[email protected]> wrote in message
news:[email protected]...
> > Hi
> >
> > I have had trouble with some code to remove the macro button from a
> > worksheet (the spreadsheet is being reduced to bare values etc for
> > distribution as a final report etc)
> >
> > By way of background, the workbook has been used by others so someone
may
> > have copied or reinstated the macro button - see below.
> >
> > I was using this code:
> > ActiveWorkbook.Sheets("Income & Expenditure A1").Shapes("Button
3").Delete
> > but I have recently got the runtime error "the item with the specified
name
> > was not found"
> >
> > I substituted this code and it seems to work OK
> > ActiveWorkbook.Sheets("Income & Expenditure A1").Shapes(1).Delete
> >
> > My question is this: will this code ALWAYS delete the one macro button
on
> > the sheet (there are no other "shapes").
> >
> > Many thanks
> >
> > Tim
> >
> >
> >
>
>