Delete Macro After Running

[Bill Oertell]

Is there any way to delete a macro after it has run successfully?

 

[Paul B]

Bill, here is one way from a previous post
If you only want to use the macro one time then
Place your macro in a module called Module1 together with the macro
deletemodule and run the sub deletemodule.
The whole Module will be deleted, remember that.

Sub deletemodule()
Yourmacro
With ThisWorkbook.VBProject.VBComponents
..Remove .Item("Module1")
End With
End Sub

Sub Yourmacro()
MsgBox "Hi"
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[Tom Ogilvy]

Look at Chip Pearson's page on the VBE

http://www.cpearson.com/excel/vbe.htm

also

http://support.microsoft.com/default.aspx?scid=kb;en-us;172109&Product=xlw
VBA: How To Delete a Sub Procedure After It Runs Once

 

[Bill Oertell]

I'm having problems with my Outlook Express, so I'm forced to keep my posts
much shorter than I'd like. But I didn't mention that the Sub I want to
delete has to run as Private in ThisWorkbook.

 

[Tom Ogilvy]

That's why you will need to use Chips page to write code to go in and delete
you procedure.

Eventually you will need to save the workbook to "seal" your deletion, but
you can't do it in the same sub. The code isn't deleted until it completes.

 

[Bill Oertell]

I've decided I don't really want to delete the macro after it runs; what if
the user wants to add another entry after they've already saved the
workbook? The macro is supposed to double check their input, and if it
self-deletes that won't be possible. This leads me to another question I'll
post in another topic in a few minutes.