HOWTO: override an Excel built-in command with a VBA macro??


Does anyone know if there is a way to override the Excel Save command with a VBA macro IN A CENTRAL WAY???

"IN A CENTRAL WAY" -  as apposed to changing the macro assignment for each save button (in file/save menu, in the save standard toolbar button, in ctrl+s).

This method seems not to fit, because:
1) I might "forget" another place that invokes the Save function, in which my macro will not be called, and I do want all save operations to "pass through my macro"
2) This method makes me manage the enable/disable state of the buttons I override; For example, if I assign my macro to the save toolbar button, then when there is no workbook open, this button remains enabled.

Thank you,
Who is Participating?
tureConnect With a Mentor Commented:

Ok - this will create your own Save procedure for ALL workbooks. You create an Add-In with Application events.

1. Start Excel with a new, blank Workbook.
2. Alt+F11 (to Visual Basic Editor)
3. Insert - Class Module
4. Press Ctrl+R and F4 to make sure that the 'Project' and 'Properties' windows are shown
5. Change the name of the class module to 'MySaveClass'
6. Tools - VBAProject Properties - General tab - Project Name: MySave - OK
7. Enter this code in 'MySaveClass'
   Public WithEvents App As Application

   Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
     'This is your own saving routine
     MsgBox "No. I refuse to save your workbook!"
     'If you use VBA code to save in this procedure, make sure to use
     '  Application.EnableEvents = False
     'before the save and
     '  Application.EnableEvents = True
     'Set Cancel to True to stop Excel from saving
     Cancel = True
   End Sub

8. Double-Click 'ThisWorkbook' and enter this code:
   Dim MySave As New MySaveClass

   Private Sub Workbook_Open()
     Set MySave.App = Application
   End Sub

9. Back to Excel (Alt+F11)
10. File - Save As - Save as type: 'Microsoft Excel Add-In' - File name: 'MySave'
11. Close and restart Excel

(I'm not sure if the following steps are necessary, but they won't hurt)
12. Tools - Add-Ins - Browse - Click 'MySave' - OK
(Perhaps you'll get a question now. In that case: answer Yes)
13. Close and restart Excel.

Done! Now try saving workbooks... :o)


1. Press Alt+F11 to go to Visual Basic Editor.
2. Double-Click 'ThisWorkbook' to show it's code window.
3. Enter your own procedure in the Workbook_BeforeSave event - something like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  'Enter your own save commands here
  'Turn event handling off to avoid the running of this
  'procedure when saving the workbook
  Application.EnableEvents = False
  'Save this workbook using a preset name and location
  ThisWorkbook.SaveAs "c:\test\testbook.xls"
  'Turn event handling back on
  Application.EnableEvents = True
  'Use Cancel=True to stop Excel from saving
  Cancel = True
End Sub

Ture Magnusson
Karlstad, Sweden
Upgrading to answer
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

YaeliBeliAuthor Commented:
Hello Ture,

Thank you for your time and effort!

I am not sure your answer helps me.
I've tried applying your answer, but as far as I could see, the Workbook.beforeSave event applies only for the CURRENT workbook.

I need the save operation to be overriden for ALL workbooks that will open "from now on" (with out changing their VBA project).

Like I said, I want all save operations to "pass through my macro".
That's why I tried to override the save button.

Is there a way to write a "global" "Workbook.beforeSave"??
In an add-in or something of the likes?

YaeliBeliAuthor Commented:
Works like a charm.
Thanks :)

I'm glad that I could help you. Thanks for the points!

All Courses

From novice to tech pro — start learning today.