Remove All VBA

  • Thread starter Thread starter Otto Moehrbach
  • Start date Start date
O

Otto Moehrbach

Excel 2007 Win 7 64-bit
I have a wb with VBA code that does various things. The code then savesAS
the wb in another name. At that point, I want the same code to remove all
code from that wb and save the wb. Can that be done and how? Thanks for
your time. Otto
 
Hi,

This would after the SaveAs routine

Sub DeleteCode()
Msg = "Are you sure you want to delete Code from " & ActiveWorkbook.Name
Response = MsgBox(Msg, vbYesNo)
If Response = vbNo Then Exit Sub
On Error Resume Next
With ActiveWorkbook.VBProject
For X = .VBComponents.Count To 1 Step -1
.VBComponents.Remove .VBComponents(X)
Next X
For X = .VBComponents.Count To 1 Step -1
.VBComponents(X).CodeModule.DeleteLines _
1, .VBComponents(X).CodeModule.CountOfLines
Next X
End With
End Sub

Mike
 
The learning curve for me with E2007 shows no sign of becoming less steep,
thanks for that tip.

Mike
 
Ron
I did that and the file now is an xlsx file. All the code is there and
it runs!! Did I miss something? Each time I save it, it warns me that I
will lose all my code. I click Yes and the code is still there. Otto
 
Ron
After saving it as an xlsx file, I closed the file. Closed Excel.
Opened Excel and the file and the code is gone. Otto
 
Thanks for posting this Otto.

Like you. I never thought of closing Excel then re-opening after saving as
*.xlsx


Gord
 
Back
Top