Remove a Macro Code

[CH]

I have the following code that: when pressing "Ctrl+E" runs a macro
(called "Prepare_External") that runs the below code. This code
protects all the worksheets, hides one particular sheet called "Input"
and then protects the workbook. But before I protect the workbook I
want to delete the macro "Prepare_External" so it cannot be run again.
How can I do this?


' Keyboard Shortcut: Ctrl+e
Sub Prepare_External()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="password"
Next ws
ActiveWorkbook.Worksheets("Input").Visible = False

????delete Macro "Prepare_External"???????????

Activeworkbook.protect password: ="password"


End Sub

 

[Joerg]

Sounds weird. You want the macro to kill itself and then go on and execute
another statement (protect the workbook)?

You can't use a macro to delete macros. You can however use a global
variable as a flag and avoid that the macro is run twice.

Joerg Mochikun

 

[kounoike]

I'm not sure this is a safe code or not, but seems to work for me, though i
don't want to use it myself and i think Joerg's suggestion is the right way
to go. Presuming your Prepare_External reside in Module1, insert a comment
like 'Delet to this line into last line, and assign macro
Onetime_Prepare_External to short cut key("Ctrl+E"). Macro
Onetime_Prepare_External will execute macro Prepare_External and delete
codes of Prepare_External.

Sub Onetime_Prepare_External()
Dim VBCodeMod As CodeModule
Dim Startline As Long, endline As Long, i As Long

On Error Resume Next
Prepare_External
With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
Startline = .ProcBodyLine("Prepare_External", vbext_pk_Proc)
endline = .ProcCountLines("Prepare_External", vbext_pk_Proc)
For i = Startline To Startline + endline
If Trim(.Lines(i, 1)) = "'Delete to this line" Then
endline = i
Exit For
End If
Next
..DeleteLines Startline + 1, endline - Startline - 1
End With
End Sub

Sub Prepare_External()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="password"
Next ws
ActiveWorkbook.Worksheets("Input").Visible = False
ActiveWorkbook.Protect Password:="password"
'Comment below is required as it is to work
'Delete to this line
End Sub

keizi

 

[CH]

Thanks for the help. this is what i'm going to use, it seems to work.

' Keyboard Shortcut: Ctrl+e
Sub Prepare_External()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="password"
Next ws

ActiveWorkbook.Worksheets("Input").Visible = False
ActiveWorkbook.Protect Password:="password"
With ThisWorkbook.VBProject.VBComponents
.Remove .Item("Module1")
End With
End Sub

 

[kounoike]

Well, if there is no other procedures in module1, i think your code is much
more easier and safer to handle your case. but i don't have my preference
for a code something like to kill itself, though i posted it myself. Good
luck!

keizi