Delete all Macros Error

[BOBODD]

I'm using the code snippet from http://www.cpearson.com/excel/vbe.htm to
delete all macros and I've inserted it into my code below.

When I run this, saves my project as a new file, then deletes most of the
sheets and deletes *some* of the vb code. It leaves 2 userforms and a module.
Any ideas?

Private Sub CmdOK_Click()
Dim MyFile As Variant
Dim MyFileName As String
Dim wks As Worksheet
Dim MyFileFilter As String
Dim SheetNames As String
Dim FullSheetNames() As String
Dim Ans As Integer, i As Integer
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
PubCol = 4
Set MyFrame = Me.Frame1
Call FillForm("Schedule", "B38", 1, 0, 45, 2)
MyQuote = ""
Call CreateQuote(5)
Sheets("Quote").Range("C28") = MyQuote
Sheets("Quote").Range("H29") = Sheets("Schedule").Range("E46")
Ans = MsgBox("Do you want to print contract forms now?", vbYesNo)
If Ans = vbYes Then
' PRINTING CODE HERE
End If
Ans = MsgBox("Do you want to save a copy of this quote?", vbYesNo)
If Ans = vbYes Then
i = 1
MyFileName = Sheets("schedule").Range("C6")
MyFileFilter = "Excel Files (*.xls),*.xls"
MyFile = Application.GetSaveAsFilename(MyFileName, MyFileFilter)
If MyFile <> False Then
ActiveWorkbook.SaveAs MyFile
For Each wks In ThisWorkbook.Worksheets
Application.DisplayAlerts = False
wks.Visible = xlSheetVisible
If wks.Name = "Schedule" Or wks.Name = "Quote" Then
wks.Protect
wks.EnableSelection = xlNoSelection
Else
wks.Delete
End If
Next
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
ActiveWorkbook.Save
Application.Quit
End If
End If
Unload Me
End Sub

 

[Chip Pearson]

You need to narrow down the code only to the relevant block of code. You
can't really expect someone to attempt to replicate your workbook,
worksheets, forms, and so on.

The piece of code that does the actual deletion of the code:

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

works fine.

Use F8 to step through your code line by line to determine the cause of the
problem. Also, be sure that the ActiveWorkbook is the correct workbook.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[BOBODD]

Sorry Chip, I'm still new to the debugging part of this. Will repost if I
still can't find the problem over the weekend

 

[Chip Pearson]

Sorry Chip, I'm still new to the debugging part of this.

You can find a good explanation of various debugging techniques at:
http://www.cpearson.com/excel/Debug.htm

 

[BOBODD]

Hi Chip,

I hope you're still monitoring this thread. I've stepped through my code
which behaves as I want, but once I reach the first line of the For Each loop
to delete the code - (Set VBComps = ActiveWorkbook.VBProject.VBComponents) -
I get the error "Can't enter break mode at this time."

This still appears to only delete some of the code from my project. As this
code is deleting itself, is it possible that this is being deleted before
looping through every component? If so, can I affect the order that this code
loops through each control?

Thanks for your help on this.