Workbook_BeforeSave not launching

T

tabea

New Member
Joined
Feb 2, 2010
Messages
4
I have Excel 2000
After reading some posts on these forums, I have written the following code.

This is the contents of my Personal.XLS file, ThisWorkbook object:

Code: 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        MsgBox "Hello BeforeSave"
End Sub
(If I delete the "Private" keyword, there is no change in behaviour)

And this it the contents of the Module1 object in Personal.xls :

Code: 
Sub Auto_Open()
    Application.EnableEvents = True
    MsgBox "Hello Auto_Open"
End Sub

When I launch Excel, it opens with a new empty file. I get a message box with "Hello Auto_Open" message. But when saving, I don't get the "Hello BeforeSave" message. Only get the standard save dialog box.

Why is my Workbook_BeforeSave macro not launched at all??

Well, in a special case it works. Namely, if after starting Excel I go to VB editor and then press Save, then the macro is launched and message box appears. It looks like VB editor must be open for the macro to fire.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Welcome to the Board.

That code is in your PERSONAL.XLS so the event will fire when you save it (you will be prompted to do so when you exit Excel if it has changed). It won't fire when you save another workbook.
 
Upvote 0
My purpose is to create a macro which will launch always when I press "Save" in Excel. It should work per Excel installation on my computer and not per any single workbook. How to achieve this?

By the way, the Auto_Open macro which is also saved in Personal.xls is launching always, not only when I open VB code. I think I've read somewhere that macros in Personal.xls are global, but I am not at all proficient in those VB stuff, so maybe I misunderstood.
 
Upvote 0
The Auto_Open is firing every time you start Excel because the Personal Macro Workbook is opened every time you start Excel. Note though that you don't see that message every time you open a workbook from within Excel - it's the same issue as the save.
The code required to trap the Save for every workbook is more complicated - can I just ask what you are trying to achieve?
 
Upvote 0
It still does not work. My real purpose is to disable saving Excel files at all on this particular computer. So the real code will be "Cancel=true" at some point in this BeforeSave procedure, but I test with MsgBox because saving VB macro which disables saving is a bit of a hassle.

The contents of my Personal.xls (ThisWorkbook) is now as follows:

Code: 
Public WithEvents App As Application
Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel as Boolean)
    MsgBox "Hello App_WorkbookBeforeSave"
End Sub
'
I also moved the Sub to Module1 leaving App declaration in ThisWorkbook, but in this case also didn't work.
 
Upvote 0
How and where did you initialise the App variable?
 
Upvote 0
I got it finally working :)
The missing steps were:
  • creating a new class module Class1 containing the App_WorkbookBeforeSave sub
  • instantiating this class in Auto_Open
  • adding App_WorkbookBeforeClose containing ActiveWorkbook.Saved=True to silently ignore all changes
Thanks for all quick responses!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Unable to Suppress Save Prompt
Replies
4
Views
295
davidam
D
C
  • Solved
My code in BeforeSave is preventing the save???
Replies
5
Views
355
Clif McIrvin
C
J
  • Question Question
Unprotect workbook, find certain RGB cells even if rows are deleted
Replies
1
Views
183
iliace
iliace
D
  • Question Question
Saving workbook "hides" macros
Replies
2
Views
716
dotsent
D
P
  • Question Question
Issue opening Outlook with VBA
Replies
1
Views
283
rlv01
R

Forum statistics

Threads
1,224,525
Messages
6,179,319
Members
452,905
Latest member
deadwings

Share this page

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
Back
Top