Deleting code at run time
 

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

Deleting code at run time
 

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

Deleting code at run time
 

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