how to save a workbook and strip it of macros?

[Reddist]

Heya, me again.

I have a worksheet set up so when the user opens it, it acts as a template. Userforms guide the user through filling it out, then invites the user to save the sheet with a new name.

How can I save the sheet and strip it of all the macros? The worksheet will be opened again (and again, and again, and...) later on, but I don't want the event-triggered macros to run more than once on the form... I'd rather they not be there at all.

Can I save the workbook, with a new name, sans macros and forms?

Thanks yet again for your help!

 

[venkat1926]

open vb editor
goto relevant modul
keep cursor in any macro code
cick file-remove module1-
click NO in the doropdown box qurery
now save the workbook.

 

[SydneyGeek]

If you REALLY are serious about deleting all code in the workbook, try this:

1. Place this code in a new module
2. Follow the steps in the comments at the top of the module
3. Save the file as a template (with a .xlt extension)
4. In the Workbook_BeforeSave event, check that the file extension is no longer .xlt and call DeleteAllVBA.

The saved file will be stripped of all VBA, but the modules and forms will be exported first. Note: Event code will be REMOVED, not just disabled.

If you wnat to stop this happening on your machine, go to the Immediate window (Ctrl + G in the VBE), type Application.EnableEvents = False and press ENTER.
You can always reactivate it by running Application.EnableEvents = True in the Immediate window

This has been adapted from code on Chip Pearson's site.

Option Explicit

'before attempting to run this code:

'1. Set a reference to "Microsoft Visual Basic For Applications Extensibility" library
'(Tools > References while in the VBE)
'2. Select View > Properties Window and name this module basDelete
'3. If you have XP or higher, go to Tools > Macro > Security,
'click the Trusted Sources tab and tick Trust Access to VBA Project

Sub DeleteAllVBA()

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

'Export forms, modules and class modules before deletion
ExportAllVBA

'delete all code EXCEPT for this module
For Each VBComp In VBComps
   Select Case VBComp.Type
      Case vbext_ct_StdModule, vbext_ct_MSForm, _
            vbext_ct_ClassModule
         If VBComp.Name <> "basDelete" Then
            VBComps.Remove VBComp
         End If
      Case Else
         With VBComp.CodeModule
            .DeleteLines 1, .CountOfLines
         End With
   End Select
Next VBComp

'now delete this module
VBComps.Remove VBComponent:=VBComps.Item("basDelete")

End Sub

Sub ExportAllVBA()
Dim VBComp As VBIDE.VBComponent
Dim Sfx As String

For Each VBComp In ActiveWorkbook.VBProject.VBComponents
    Select Case VBComp.Type
        Case vbext_ct_ClassModule, vbext_ct_Document
           Sfx = ".cls"
        Case vbext_ct_MSForm
           Sfx = ".frm"
        Case vbext_ct_StdModule
           Sfx = ".bas"
        Case Else
           Sfx = ""
    End Select
    If Sfx <> "" Then
       VBComp.Export _
          Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx
    End If
Next VBComp
End Sub

Denis