This article might help. It has code for programatically removing VBA code.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=93

Stan Shoemaker
Palo Alto, CA

"PM" wrote:

Hi !

To achieve such a task, I start by copying the sheets like this :

Set XLS = ThisWorkbook
XLS.Sheets(1).Copy
Set ARR = ActiveWorkbook
For i = 3 To 9
XLS.Sheets(i).Copy After:=ARR.Sheets(ARR.Sheets.Count)
Next

This does a good job of copying fast and eliminating code modules (macros).
But the code inside the sheets remains (such as SelectionChange, etc.). End
users receiving these sheets would be confronted with countless error
messages.

I therefore resolve to delete the sheets' code with the following :

With ARR.VBProject
... etc. see below if interested
End With

Well, upon hitting the first of these lines, I get error message 1004
"Accessing the VB Project by program is unreliable". Upon another go (F8) :
the VBProject method ... has failed.

Then I thought (foolishly...) that I might perhaps fix this by saving the
target workbook first. The program then passes the critical line (first line
above) only to jump later in the same routine flying past the end with (last
line above) and even past an exit sub and landing after the first label.

This is way beyond my limited capacity.
Any miracle doctors recognize these symptoms ?
Have a fix, one way or another ? I would be most grateful...

Pat


The entire code I used (which originates from this newsgroup, I believe) :

With ARR.VBProject
For Each O In .VBComponents
Select Case O.Type
Case 1, 2 ' standard or class module
MsgBox "Module " & O.Name
.VBComponents.Remove O
Case Else ' form or document
With O.CodeModule
If .CountOfLines 0 Then
MsgBox "Module behind " & O.Name
.DeleteLines 1, .CountOfLines
End If
End With
End Select
Next
End With