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

Hello,

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,
Yael.
YaeliBeliAsked:
Who is Participating?
 
tureConnect With a Mentor Commented:
Yael,

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
     'afterwards.
 
     '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)

/Ture
0
 
tureCommented:
Yael,

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
0
 
tureCommented:
Upgrading to answer
0
The new generation of project management tools

With monday.com’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!

but..
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?

0
 
YaeliBeliAuthor Commented:
Works like a charm.
Thanks :)
0
 
tureCommented:
Yael,

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

/Ture
0
All Courses

From novice to tech pro — start learning today.