Remove a Macro Code

  • Thread starter Thread starter CH
  • Start date Start date
C

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
 
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
 
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
 
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
 
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
 
Back
Top