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").C odeModule
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?