I have a workbook_open macro included in the global workbook object
("ThisWorkbook"). This currently calls a macro to run upon opening the
workbook, after which the module "Self-destructs" by deleting itself, but I'm
still left with the workbook_open code so people get that "This workbook
contains macros..." popup when they open the sheet. Is there a way for this
module to also clear the workbook_open code located in "ThisWorkbook"?

Hi Dave
Use this code below, it will clear all code from your workbook:
Dim VBC As VBComponent
With ActiveWorkbook.VBProject
For Each VBC In .VBComponents
If VBC.Type = 100 Then
With VBC.CodeModule
.DeleteLines 1, .CountOfLines
.CodePane.Window.Close
End With
Else: .VBComponents.Remove VBC
End If
Next VBC
End With

HTH
Cordially
Pascal

"Dave" a écrit dans le message de news:
...
I have a workbook_open macro included in the global workbook object
("ThisWorkbook"). This currently calls a macro to run upon opening the
workbook, after which the module "Self-destructs" by deleting itself, but
I'm
still left with the workbook_open code so people get that "This workbook
contains macros..." popup when they open the sheet. Is there a way for
this
module to also clear the workbook_open code located in "ThisWorkbook"?

Papou,
It seems to not like the "Dim VBC as VBComponent" line, I get the following
error:

Compile error:
User-defined type not defined

Any suggestions?

"papou" wrote:

Hi Dave
Use this code below, it will clear all code from your workbook:
Dim VBC As VBComponent
With ActiveWorkbook.VBProject
For Each VBC In .VBComponents
If VBC.Type = 100 Then
With VBC.CodeModule
.DeleteLines 1, .CountOfLines
.CodePane.Window.Close
End With
Else: .VBComponents.Remove VBC
End If
Next VBC
End With

HTH
Cordially
Pascal

"Dave" a écrit dans le message de news:
...
I have a workbook_open macro included in the global workbook object
("ThisWorkbook"). This currently calls a macro to run upon opening the
workbook, after which the module "Self-destructs" by deleting itself, but
I'm
still left with the workbook_open code so people get that "This workbook
contains macros..." popup when they open the sheet. Is there a way for
this
module to also clear the workbook_open code located in "ThisWorkbook"?

Got it, I had to change the declaration of "VBC" to an Object and it worked:

Dim VBC As Object
With ActiveWorkbook.VBProject
For Each VBC In .VBComponents
If VBC.Type = 100 Then
With VBC.CodeModule
.DeleteLines 1, .CountOfLines
.CodePane.Window.Close
End With
Else: .VBComponents.Remove VBC
End If
Next VBC
End With



"Dave" wrote:

Papou,
It seems to not like the "Dim VBC as VBComponent" line, I get the following
error:

Compile error:
User-defined type not defined

Any suggestions?

"papou" wrote:

Hi Dave
Use this code below, it will clear all code from your workbook:
Dim VBC As VBComponent
With ActiveWorkbook.VBProject
For Each VBC In .VBComponents
If VBC.Type = 100 Then
With VBC.CodeModule
.DeleteLines 1, .CountOfLines
.CodePane.Window.Close
End With
Else: .VBComponents.Remove VBC
End If
Next VBC
End With

HTH
Cordially
Pascal

"Dave" a écrit dans le message de news:
...
I have a workbook_open macro included in the global workbook object
("ThisWorkbook"). This currently calls a macro to run upon opening the
workbook, after which the module "Self-destructs" by deleting itself, but
I'm
still left with the workbook_open code so people get that "This workbook
contains macros..." popup when they open the sheet. Is there a way for
this
module to also clear the workbook_open code located in "ThisWorkbook"?

Dave
Sorry for this late reply.
Yes you may as well use an Object declaration.
But the other alternative which I forgot to mention was to add a reference
to
"Microsoft Visual Basic For Application Extensibility 5.3"
And in which case the original code would work.

Cordially
Pascal

"Dave" a écrit dans le message de news:
...
Got it, I had to change the declaration of "VBC" to an Object and it
worked:

Dim VBC As Object
With ActiveWorkbook.VBProject
For Each VBC In .VBComponents
If VBC.Type = 100 Then
With VBC.CodeModule
.DeleteLines 1, .CountOfLines
.CodePane.Window.Close
End With
Else: .VBComponents.Remove VBC
End If
Next VBC
End With



"Dave" wrote:

Papou,
It seems to not like the "Dim VBC as VBComponent" line, I get the
following
error:

Compile error:
User-defined type not defined

Any suggestions?

"papou" wrote:

Hi Dave
Use this code below, it will clear all code from your workbook:
Dim VBC As VBComponent
With ActiveWorkbook.VBProject
For Each VBC In .VBComponents
If VBC.Type = 100 Then
With VBC.CodeModule
.DeleteLines 1, .CountOfLines
.CodePane.Window.Close
End With
Else: .VBComponents.Remove VBC
End If
Next VBC
End With

HTH
Cordially
Pascal

"Dave" a écrit dans le message de
news:
...
I have a workbook_open macro included in the global workbook object
("ThisWorkbook"). This currently calls a macro to run upon opening
the
workbook, after which the module "Self-destructs" by deleting itself,
but
I'm
still left with the workbook_open code so people get that "This
workbook
contains macros..." popup when they open the sheet. Is there a way
for
this
module to also clear the workbook_open code located in
"ThisWorkbook"?