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
Similar Threads
