Many thanks.
I think my follow-up post may indicate that I
was 'jumping' between modules, which doesn't
seem very logical when I think about it <g.
Regards.
"Bob Phillips" wrote in message
...
Haven't tested it, but is the code that is doing the deleting getting
deleted before it finishes?
Try testing for the ThisWorkbook code module, and bypassing that delete,
and
then specifically delete that at the end.
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Stuart" wrote in message
...
Chip Pearson gives the following code on his website to
remove all VBA code in a Project:
Private Sub DeleteAllVBA()
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
Set VBComps = ActiveWorkbook.VBProject.VBComponents
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
End Sub
For this to work, I have set a reference to MS Visual Basic
For Applications Extensibility 5.3. I'm currently using this
with Excel 2000 under Win 2k.
This code is in the ThisWorkbook module, and runs
under a Workbook Before_Save event where a test is
made to determine if the user's work is done. If so, then
the 'strip VBA' code runs.and then saves the workbook
....except it doesn't run properly.
I get the following mixture of results:
1. It works
2. It closes the workbook without errors but does not
delete the code
3. It closes the workbook, then closes excel.
4. I get this error:
Compile error: expected End Property
If I Ok that message, it asks if I want to save changes
to the workbook, so I Cancel. Module1 has been
removed and the ThisWorkbook module is empty.
Can anyone point to my errors, please?
Regards.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.712 / Virus Database: 468 - Release Date: 27/06/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.712 / Virus Database: 468 - Release Date: 27/06/2004