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

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" wrote in message
...
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

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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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