how to save a workbook and strip it of macros?

Reddist

New Member
Joined
Jul 28, 2005
Messages
12
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!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
:eek: :eek: 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.

Code:
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
 

Forum statistics

Threads
1,136,589
Messages
5,676,679
Members
419,644
Latest member
KeelsM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top