Excel "Delete everything" Code not deleting the module

[Mack2]

In one of my macro's (macro1) I need all of the code deleted because we are sending the results to a customer. I created another macro that opens macro1 and deletes all of the code, and it works fine. When I add code to close the workbook, it does not delete the module, only the workbook code. If I manually close the workbook (click on the x), the module has been deleted.....So I need the code that will open the workbook and close it, but deletes everything within the workbook.
THANKS! I know this is a crazy one

 

[TonyJollans]

I do wonder just why you have the code in there in the first place, but ...

I can't properly follow all that you say. Unless you have 2007 (see below) modules will never be automatically deleted. Can you be a bit more precise - and also say what module the code is/was in?

If you need it, code for deleting modules is something like:

[tt]With [Workbook_Reference].VBProject
.VBComponents.Remove VBComponents("Module_Name")
End With[/tt]

For reference:

In 2007, if you comment out everything in a module and then save the workbook (as macro-enabled), the module will be automatically deleted with no warning. This is, apparently, by design. I'm betting most everybody will get caught at least once by this.






Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Mack2]

Hi TonyJollans

Basically the problem is My code deletes all of the code within the workbook fine. But any code I write to close the workbook, readds module1 when it is closed. If I close the workbook manually, the module stays deleted. I am using 2003. Below is the code that works to delete everything. So basically, how do I use code to delete everything, and then close the workbook progomatically?

Public Sub DeleteEverything()
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
Dim CurrentWorkbook As String

Set VBComps = ActiveWorkbook.VBProject.VBComponents

Application.DisplayAlerts = False

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

 

[TonyJollans]

When you close the workbook make sure you save the changes ([Workbook_Ref].Close SaveChanges:=True). I think maybe you are not specifying that - and you would be prompted except for having DisplayAlerts set to False.


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association