Self deleting Macro!!! is this possible?

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

Use your FIND tool in IE to jump to the section labeled "Deleting All VBA Code In A Project"

Sub M3CleanMods()
'Remove any Sub not listed below!
'Standard Module code, like: Module3.
'Note: You will need to create a reference to MS VBA Extensibilty 5.3
'(in VBE go to Tools>References) to work this code, it was set in this version!

Dim VBComp As VBComponent
Dim VBCodeMod As CodeModule
Dim StartLine As Long, Line1 As Long, Lines As Long
Dim myList1 As String, myList2 As String, myCode As String

'Get modules!
For Each VBComp In ActiveWorkbook.VBProject.VBComponents

'Option: To limit Sub removal to this type of Module [If]!
'If VBComp.Type = vbext_ct_StdModule Then

'Get Sub's name [VBCodeMod]!
Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
'List Sub's name [myCode]!
myCode = .ProcOfLine(StartLine, vbext_pk_Proc)
StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)

'Build all Sub's list [myList1]!
myList1 = myList1 + " " + vbCr + VBCodeMod + ": " & myCode

'Do not remove these Sub's!
'For best operation each Sub's name should be unique!
If myCode = "M1Test1" Then GoTo myLoop
If myCode = "S1Test1" Then GoTo myLoop
If myCode = "M2deMods" Then GoTo myLoop
If myCode = "M2Remov" Then GoTo myLoop
If myCode = "TWmySh3" Then GoTo myLoop
If myCode = "M3CleanMods" Then GoTo myLoop

'Build deleted sub's list [myList2]!
myList2 = myList2 + " " + vbCr + VBCodeMod + ": " & myCode

'Delete correct Sub!
With VBCodeMod
Line1 = .ProcStartLine(myCode, vbext_pk_Proc)
Lines = .ProcCountLines(myCode, vbext_pk_Proc)
.DeleteLines Line1, Lines
End With

myLoop:
Loop
End With

'Option: To limit Sub removal to this type of Module [End]!
'End If

Next VBComp
'Show results!
MsgBox "These Components have been found:" & vbCr & myList1 & vbCr & _
vbCr & vbCr & "These Components have been deleted:" & vbCr & myList2
End Sub

Sub ModuleClear()
'Run from module1.
'Place this self-destructing macro in its own module.
'The code simply deletes this module after the code has completed.

' Make sure access to the VBProject is allowed
Dim VBP As Object ' as VBProject
If Val(Application.Version) >= 10 Then
On Error Resume Next
Set VBP = ActiveWorkbook.VBProject
If Err.Number <> 0 Then
MsgBox "Your security settings do not allow this procedure to run." _
& vbCrLf & vbCrLf & "To change your security setting:" _
& vbCrLf & vbCrLf & "Select Tools - Macro - Security." & vbCrLf _
& "Then, click the 'Trusted Sources' tab" & vbCrLf _
& "and place a checkmark next to 'Trust access to Visual Basic Project.'", _
vbCritical
Exit Sub
End If
End If

'The code to run just once.
MsgBox "I am a macro, that runs just one time and self destructs!" & _
vbCr & vbCr & "Removing all traces of my existence in the process!" _
, , "Click OK to delete all traces of this code!"

'The code to destroy the code and module!
With Application.VBE.ActiveVBProject
.VBComponents.Remove .VBComponents("Module1")
End With
End Sub

thanks all....joe if i could just ask, im a serious newbie in vba, just trying to understand all thats happening, do i need to use the first or second thread, or both, just reading thru it all and its all a bit 'serious' looking lol


thanks in advance


bronny

sorry as in if i have a blank workbook with like sheet1, sheet2, etc

how would i modify your code joe to apply this

thanks bronny

The last post code is code to remove itself so you would also imbed the actual code you want to run in there as well, you could use the "Open" or some other Event to run your code and then delete it automatically.

The first post lets you list the macros you want to keep, all others will be wiped out. so it works a little deferently.

Be very careful with this type of code, that is any code that works with making or deleting system components, like: deleting files, working with the regestry, file shell, code pages or like this one deleting macros. You could damage or remove a lot more than you think. This code is the type of code that could be used to make virus like code by bad people.

You indicated that you are new to VBA, so I would get better at safer coding before I would mess with this family of coding!

Also, as a note: If the user has the Excel security set to high, All code is striped from the workbook automatically so none of your code will ever run!
So, to answer your other question: You can't do it!

Joe Was said:

Also, as a note: If the user has the Excel security set to high, All code is striped from the workbook automatically so none of your code will ever run!

Click to expand...

Just for future readers, I'd footnote Joe's comment to say that this is true unless your WB is digitally signed by you and the user has your digital signature loaded as a trusted source. But that's another kettle of fish...

And I would strongly second Joe's opinion that if you're a newbie at VBA and especially if you're a newbie at programming altogether, I'd get quite a bit more coding under my belt before I'd start messing around with programmatically deleting code from projects. Very easy to make a mell of a hess.

thanks for all responses guys

bronny