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

Deleting a Button on a Worksheet
 

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

Deleting a Button on a Worksheet
 

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