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" wrote in message ...
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" wrote in message
...
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" wrote in message
...
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