Help on Stripping VB Code from SaveAS

[Mark Kellythorn]

Hi All,

I have Spent ages Programming a template file and am using a UserForm which
runs on BeforeSave event on the Workbook. What I would like to happen is
that when the new file is saved it strips out all vb code that is active in
the template instead of saving it with the new file.

Any Ideas?

Thanks in Advance

Mark

[Jim Rech]

Add this code to your template and call it in your before save event
handler.

You must of course save the template without this code running. You might
add a check whether the active workbook's name has a file extension.

''Needs a reference to the VB Extensibility library set
'Removes from active workbook all:
''Regular modules
''Class modules
''Userforms
''Code in sheet and workbook modules
''Non built-in references
''Excel 4 macro sheets
''Dialog sheets
Sub RemoveAllCode()
Dim VBComp As Object, AllComp As Object, ThisProj As Object
Dim ThisRef As Reference, WS As Worksheet, DLG As DialogSheet
Set ThisProj = ActiveWorkbook.VBProject
Set AllComp = ThisProj.VBComponents
For Each VBComp In AllComp
With VBComp
Select Case .Type
Case vbext_ct_StdModule, vbext_ct_ClassModule,
vbext_ct_MSForm
AllComp.Remove VBComp
Case vbext_ct_Document
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End Select
End With
Next
For Each ThisRef In ThisProj.References
If Not ThisRef.BuiltIn Then ThisProj.References.Remove ThisRef
Next
Application.DisplayAlerts = False
For Each WS In Excel4MacroSheets
WS.Delete
Next
For Each DLG In DialogSheets
DLG.Delete
Next
End Sub

--
Jim Rech
Excel MVP