Self-deleting macro - is this prossible?

[David]

I've written a macro that deletes unused tabs in an Excel spreadsheet
to save disk space before it is saved under a new name. I would also
like the macro to "self destruct" to further save space and eliminate
the annoying message regarding macro security when the now-condensed
file is reopened later. The security message/setting is required by my
company, so setting the security lower is not an option. Any
suggestions? I've searched everywhere for the code and found none.
Attempting to record these actions on Excel just produces an empty
macro. Thanks for any help.

David

 

[Ron de Bruin]

Hi David

Yes this possible

You can run a macro that delete the whole module where
you store the macro's after ir run the code
This example use "Module1"

Sub test()

' your code

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

 

[David]

Ron,

I'm sorry, but this solution did not work. I got an error message
stating "Compile error: Argument not optional". I tried using the
RemoveItem command, but this would not work either as it only applies
to list boxes. I'm open for other ideas... this has plagued me for a
year now!

David

 

[Ron de Bruin]

Working for me

Add this sub in a module called Module1 in a new workbook
to test it.
If you run the macro you see the MsgBox and after
that your module will be deleted


Sub test()

MsgBox "place your code here"

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

 

[David]

Ron,

You are absolutely right... it worked on a blank worksheet. Why it
didn't work on the sheet I already has is beyond me, but I'm going to
look at it again this morning. I have a feeling it may be in WHERE I
have the code placed within the macro. I appreciate your continued
effort to help me. If you are ever in the Little Rock, Arkansas
area... I owe you a dinner!

David

 

[David]

Ron,

I put the code in a different location within the macro, and that
seemed to do the trick. Apparently I had placed it before some other
code in the macro that prevented it from running. It worked fine this
time. Again, my sincere thanks for ending a year of torment for me. I
have several other applications that I can use this code for.

David

 

[Ron de Bruin]

Hi David

You are welcome

Thanks for the feedback