Hello,

I would like to remove a macro from a workbook after it has run.

Any thoughts?

Seems a bit drastic but here goes, put this in your macro:-

Sub macrosuicide()
Dim vbCom As Object
MsgBox "There's no going back now I hope you have a backup --DELETING--"
Set vbCom = Application.VBE.ActiveVBProject.VBComponents
vbCom.Remove VBComponent:=vbCom.Item("Module1") '<Change as required
End Sub

Mike

"swansong" wrote:

> Hello,
>
> I would like to remove a macro from a workbook after it has run.
>
> Any thoughts?

Hi Swansong,

Try the code posted by Chip Pearson:

'=============>>
Sub SelfDestruct()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

'Your code

Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
With VBCodeMod
StartLine = .ProcStartLine("SelfDestruct", vbext_pk_Proc)
HowManyLines = .ProcCountLines("SelfDestruct", vbext_pk_Proc)
.DeleteLines StartLine, HowManyLines
End With

End Sub
'<<=============


See also Chip Pearson's Pogramming to the VBE page at:

Programming To The Visual Basic Editor
http://www.cpearson.com/excel/vbe.htm

Note Chip' instructions to set a reference to the VBA
Extensibility library.


---
Regards,
Norman

That works! Just had to change some security settings in excel.

"Mike H" wrote:

> Seems a bit drastic but here goes, put this in your macro:-
>
> Sub macrosuicide()
> Dim vbCom As Object
> MsgBox "There's no going back now I hope you have a backup --DELETING--"
> Set vbCom = Application.VBE.ActiveVBProject.VBComponents
> vbCom.Remove VBComponent:=vbCom.Item("Module1") '<Change as required
> End Sub
>
> Mike
>
> "swansong" wrote:
>
> > Hello,
> >
> > I would like to remove a macro from a workbook after it has run.
> >
> > Any thoughts?