When I run that code without the reference, I get a user defined type not
defined error. I suspect Ken has commited a typo and declared
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponent
rather than
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
with the "s" on the second line as shown at Chip's site. When I use the
incorrect declaration, I can reproduce the type mismatch error on the line
cited.
--
Regards,
Tom Ogilvy
"Bob Phillips" wrote in message
...
You have to set a reference to the Microsoft Visual Basic for Applications
Extensibility library in the VBE (ToolsReferences)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Ken Loomis" wrote in message
...
I tried using this from Chip's site:
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
but I get an error 13 Type mismathc on this line:
Set VBComps = ActiveWorkbook.VBProject.VBComponents
and jiust figured it woul dbe easier to modify the code to just remove
the
lines of code from ThisWorkbbok, but I do not know how to reference that
in
this line:
Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents("NewModule").C odeModule
"Ron de Bruin" wrote in message
...
Hi Ken
There is code on Chip's site to delete all code in the project also.
Look again on the site
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Ken Loomis" wrote in message
...
After creating a report, I use the following to delete all the code
in
the main module before passing it along:
Sub DeleteAllCodeInModule()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long
Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents("Module3").Cod eModule
With VBCodeMod
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With
End Sub
Now I realize I need to also delete all the code that executes when
the
workbook is first opened. How can I modify the code above to delete
the
lines of code in ThisWorkbook?