Removing Macro/Deleting a Button in a Spreadsheet

[pnoble]

I am writing a macro in Excel 2000 that begins with a template, then
generates a copy (with SaveCopyAs) as a second template specific to a
project, and finally produces a copy of the second spreadsheet without
the macro attached...but I haven't been able to remove the macro.

I created a Control Button on the spreadsheet to activate the macro of
the original template which appear again in the second generation
template. The final copy is produced with the Copy command and also
contains the button. I wrote code to delete all the procedures and
macros that were attached to this third workbook, but when I open the
workbook, it asks me the "Disable/Enable" question. If I were to delete
the buttons, would this eliminate the workbook's link to macros? As of
now, the buttons exist in the spreadsheet, but pressing them does
nothing.

The following two segments of code I found from other posts, but they
did nothing or didn't work at all:

Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.FormControlType = xlButtonControl Then
shp.Delete
End If
Next shp
'*********************************************
For Each B In ActiveSheet.Buttons
B.Delete
Next B

If deleting the buttons doesn't work, then how else should I make a
copy without copying over the macro? I've tried copy/pasting, but
couldn't do it between two different workbooks.

Sorry for the long-winded question. Hopefully, it's understandable. Any
suggestions are appreciated.

Thanks,
Patrick

 

[Johnny]

I do a similar thing with templates, but instead of putting the code in
the template, I have a separate workbook that creates all the
templates/workbooks for me. That way, I can produce these spreadsheets
with nothing in them but the raw data and all the necessary formulas,
etc.

Please let me know if you would like me to elaborate/send you examples.

Thanks,
Johnny

 

[pnoble]

Thanks for the tip. I'll probably use that method for my next template,
but I already have so much code wrapped up in making the template
adaptable so I'd like keep it the way it is for now. But if nothing
else works, I might just have to change it to fit what you're
describing.

Thanks,
Patrick

 

[GregR]

Johnny, I would love for you to send me some examples. TIA

 

[Ron de Bruin]

See
http://www.rondebruin.nl/menuid.htm

And be sure that you also delete code and modules
http://www.cpearson.com/excel/vbe.htm

 

[Ron de Bruin]

Oops, wrong link

Use this
http://www.rondebruin.nl/controlsobjectsworksheet.htm