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?

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?

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?

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?

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?