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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
YaeliBeliAuthor Commented:
Works like a charm.
Thanks :)
0
tureCommented:
Yael,

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

/Ture
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.