Remove All VBA

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
 
M

Mike H

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
 
M

Mike H

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

Mike
 
O

Otto Moehrbach

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
 
O

Otto Moehrbach

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
 
G

Gord Dibben

Thanks for posting this Otto.

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


Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top