Deleting code at run time

[Sandy]

I have two procedures, "Workbook_Open", and "Workbook_BeforeClose" in the
"ThisWorkbook" module. Is it possible to delete these procedures prior to
closing the workbook. In other words in my "save as" version I don't want
the code to run when the file is opened or closed.
Sandy

 

[Bernie Deitrick]

Sandy,

Try the code below, which requires a reference to MS VBA extensibility.

Note that the net result is that it simply comments out the code: I have never successfully deleted
code. Trying to delete code just isn't stable, in my experience.

HTH,
Bernie
MS Excel MVP

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myBook As Workbook
Dim myVBA As VBIDE.VBComponent
Set myBook = ActiveWorkbook
Set myVBA = myBook.VBProject.VBComponents(myBook.CodeName)

With myVBA.CodeModule
For j = 1 To .CountOfLines
temp = "'" & .Lines(j, 1)
.DeleteLines j
.InsertLines j, temp
Next j
End With

Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

End Sub

 

[Sandy]

Absolutely brilliant. Where do you guys learn all this stuff?
Sandy