Asp Forum - Delete all Macros Error - microsoft.public.excel.programming

BOBODD

12/15/2006 12:34:00 AM

I'm using the code snippet from http://www.cpearson.com/exc... 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

4 Answers

Chip Pearson

12/15/2006 1:55:00 AM

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" <BOBODD@discussions.microsoft.com> wrote in message
news:2F6615BB-E02C-423E-A3CE-5A5888F8B16A@microsoft.com...
> I'm using the code snippet from http://www.cpearson.com/exc... 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

BOBODD

12/15/2006 3:00:00 AM

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" wrote:

> 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.

Chip Pearson

12/15/2006 3:27:00 AM

> 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...

"BOBODD" <BOBODD@discussions.microsoft.com> wrote in message
news:911A4A59-D98F-4DAA-9F15-2F927A23955E@microsoft.com...
> 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" wrote:
>
>> 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.
>

BOBODD

12/17/2006 11:04:00 PM

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.

"Chip Pearson" wrote:

> > 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...
>
>
>
>
>
> "BOBODD" <BOBODD@discussions.microsoft.com> wrote in message
> news:911A4A59-D98F-4DAA-9F15-2F927A23955E@microsoft.com...
> > 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" wrote:
> >
> >> 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.
> >
>
>
>

microsoft.public.excel.programming

Delete all Macros Error

BOBODD

Chip Pearson

BOBODD

Chip Pearson

BOBODD

x Login to ForumsZone