Hi there

I want to delete some code from a PRIVATE SUB in the sheet code after it has
been used. I copied the following code from Chip Pearson's web site but it
comes up with an error (it doesn't seem to like VBIDE very much). I have
checked the trust VB code checkbox in security options.

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
My preference would be just to delete the code rather than the whole VB
Project, but it doesn't really matter. My understanding is that you can't
delete code from a Private sub anyway


Sub DeleteAllCodeInModule()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents("Worksheet_Cha nge").CodeModule
With VBCodeMod
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With
'Private Sub Worksheet_Change
End Sub

This doesn't work either.


Can someone help please?