Problem with Save & Close macro and beforeSave event

**Danny_** · 05-17-2013, 12:18 PM

Hi all

I have a routine in a workbook that is causing me quite a headache.

I have created and attached a simple version of the workbook for you to check out, but basically it pans out like this...

Excel 2007 does not support the AfterSave event as far as I'm aware, so I have included the generic BeforeSave event which cancels the users' original save request and allows me to perform some functions etc. This is all fine while the user saves the workbook in the standard way.

I have then added a "Save & Close" button which runs a simple macro to save and close the file. The ThisWorkbook.Save command triggers the BeforeSave event and certain functions are performed, but, the workbook does not save and certain functions to do with manipulating objects do not work e.g. hiding a WorkSheet.

I can make the WorkBook save by adding a second ThisWorkbook.Save command which is triggered when the program returns from the BeforeSave routine (I wrap it in Application.EnableEvents = False this time). The problem is that still certain activities do not trigger.

I guess the best way is to check out the code in the attached and hopefully someone may be able to explain what is going on. You will have to excuse my lack of knowledge, I'm kind of self-teaching VBA at the moment.

Code exists in Module 1 and ThisWorkbook

Many thanks in advance

Danny_

**Leith Ross** · 05-17-2013, 03:07 PM

Hello Danny_,

It appears you either got some bad information about the event or got confused in the process. You actually don't need this event to save the workbook. You can control the entire process from your command button.

Here is the code to save the workbook to a location of the user's choosing or cancel the save. The workbook will be saved as a 92 - 2003 xls file. The attached workbook contains this change. The BeforeSave event code has been removed as well.

Please Login or Register  to view this content.

**Danny_** · 05-18-2013, 07:33 AM

Thank you for the response, Leith.

The issue is that I do have some events that need to run both before and after the file is saved. I have made an amend to your code below to show the kind of things that need to happen both before and after the save routine triggers...

Please Login or Register  to view this content.

The thing is that these validation checks etc. can only be run at the points of just before and just after the save event.

I have updated my original WorkBook which hopefully will make the issue more visible. I have removed the workbook.close element for the purpose of this query.

To summarise what I see as happening. If the user saves the file using the standard excel save icon, the beforesave event fires and all validations and sheet manipulations work as intended. If the user saves the workbook using the button/macro then the beforesave event again fires and the validations etc. work as intended but object manipulation - worksheet protect/hide etc. - do not work.

Danny

**Leith Ross** · 05-18-2013, 01:23 PM

Hello Danny_,

I don't see any difference between this workbook and the first. My advice is make your life easier and ignore any application call to save the workbook event. This can be done by having your button check a Public variable (Declared in a Module). This variable's value will be set and changed by your code only. The BeforeSave event can check this variable's value to determine if the user press pressed your button. If so then execute the code needed to and reset your variable when done, otherwise leave the event.

**Danny_** · 05-18-2013, 04:32 PM

Unfortunately, the team using this workbook insist that the user has the option of saving the file using both methods, the button/macro and the standard Excel method.

The more I think about it however, I could move the the save routine to a standard module, as you recommend, and then within the beforesave event make a call to the save macro. I believe in this way, the workbook will save using the macro routine, however the user chooses to save the workbook.

I'm off to play with it

Thanks again for your input

Danny_