Unable to delete code module from VBA project

[goblin]

I'm trying to delete all macros from an open workbook and for some reason it doesn't work. I got the code from Chip Pearson's site and it worked fine in XL2000, but now doesn't work in XL 2002.

I have enabled the 'Trust access to VB project' under options->security.

I need to use 'current file' as a template file for a lot of other files, except the other files should not have the macros that the original file has. It goes something like:

Function PrepareStrippedTemplate() As Workbook
    Dim wb As Workbook
    ThisWorkbook.SaveCopyAs Application.DefaultFilePath & "\BudgetTmpl.xls"
    Set wb = Workbooks.Open(Application.DefaultFilePath & "\BudgetTmpl.xls", False, False)
    wb.Sheets("tables").Range("A2", .Range("M2").End(xlDown)).Clear
    DeleteAllVBA wb
    Set PrepareStrippedTemplate = wb
End Function

DeleteAllVBA is like this:

Sub DeleteAllVBA(wb As Workbook)
    Dim VBComp As VBIDE.VBComponent
    Dim VBComps As VBIDE.VBComponents
    
    Set VBComps = wb.VBProject.VBComponents
    
    For Each VBComp In VBComps
        Select Case VBComp.Type
            Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule
                With VBComp.CodeModule
                    .DeleteLines 1, .CountOfLines
                End With
                VBComps.Remove VBComp
            Case Else
                With VBComp.CodeModule
                    .DeleteLines 1, .CountOfLines
                End With
        End Select
    Next VBComp
End Sub

This code clears all code from all modules, but it doesn't remove modules itself. Thus when the template file is used, it will always display the macro warning. How do I remove that code module?????!!!!

 

[jobohm]

Wow. That's great since it solves my problem, and I'm not so great since i don't get why it is this way.
Thanks a lot for sharing Ace!

 

[McGran]

This still didn't work for me but I have found a solution to my variation on this problem.

Setting

Application.EnableEvents = False

prior to opening each file.

In my case the files had Workbook_Open() code that used the very module I was trying to replace. As a result all attempts to remove the module failed. By preventing this code from executing I was able to remove the module with no further problems.

Remember to set EnableEvents back to True before you finish.

 

[hutemesr]

Above approach works for me. Thanks for the tip.

 

[goncstate93]

This still didn't work for me but I have found a solution to my variation on this problem.

Setting

Application.EnableEvents = False

prior to opening each file.

In my case the files had Workbook_Open() code that used the very module I was trying to replace. As a result all attempts to remove the module failed. By preventing this code from executing I was able to remove the module with no further problems.

Remember to set EnableEvents back to True before you finish.

McGran, thank you so much! I was about to go crazy and had tried almost everything else I could find. Your solution worked perfectly!