Workbook_BeforeSave problems with saving

Hi,

I have a problem with some of my code which has baffled me for ages & was hoping someone can help me resolve the issue.

I have a BeforeSave macro that works ok when you save manually, however there are 2 other instances that do not work:

1) Using "ThisWorkbook.Save" in another macro does not save the changes. (when you close the workbook and answer no to saving changes you lose changes that were made before running the "ThisWorkbook.Save")

2) If i have a 2nd workbook open at the same time I get stuck in an eternal loop when asked if i want to save changes when closing the workbook. (just keep getting the dialog box asking if i want to save changes, click yes and it comes back)

I hope I have explained this clearly and really do appreciate any advice that can be given

BeforeSave Code:

Please Login or Register  to view this content.

Regards
Steve

I suspect that this may be a problem:

Please Login or Register  to view this content.

You are in an event handler, the BeforeSave event, in fact, and are trying to save the workbook. I would expect that to fire the BeforeSave event again.

I would suggest that you try:

Please Login or Register  to view this content.

No guarantees, but it should help. Try putting some Stops in the events and stepping through to see what happens.


Regards, TMS

Is the other module in PERSONAL.XLSB or another sheet? ThisWorkbook refers to the workbook the macro is writen in.

also get rid of

Please Login or Register  to view this content.

This Is the original save request you are just executing some code before it saves.

Originally Posted by Steve@TRW

Hi Leon,
I had canceled the save at the begining as i wanted to set the workbook back to a certain 'open workbook state' before the save happens, then save after open state is set. The reason I have done this is so if the workbook is opened by a user with there excel set to disable macros they see the page that the before save event has set (It just tells them to turn on macros & gives instructions on how to do that).

But this is a BEFORE SAVE event so all the code in that macro will run before excel saves the cancel = true doesn't stop the save there and then.
when a user clicks save:
Excel "I would like to save",
VBA "ok but first do this" (your Workbook_BeforeSave runs)
Excel "now can I save?",
VBA "cancel = true, so no"

My apologies. I completely glossed over that! I would, however, be inclined to limit switching the event handling off and on to the immediate area that would trigger the event.

The other thing that can happen is that you can switch event handling off and on in an "outer" module and do the same in an "inner" module. Depending what the subsequent code in the outer module does, event handling may have been switched on earlier than you had intended.

I think that, because you set Cancel=True, you are effectively cancelling the save ... hence, when the user says, "no, don't save", that's exactly what happens.

Probably more random ideas than a concrete suggestion ...

Regards, TMS

Hi Leon,

I understand what you are saying however if i remove the cancel command from the before save event, i do not get the required result.

If i leave it in, it works as required when i manually save, just not when I saving calling the ThisWorkBook.Save in a seperate module.


The BeforeSave event is doing the following:

1) Cancel the original save (I do this as I do not want to save all of the BeforeSave event)

2) Take note of current active worksheet

3) Show/select "open worksheet" & hide all others

4) Now save with ThisWorkBook.Save (enable events is set to false so wont recall BeforeSave event again)

5) After saving, return to worksheet that was open before save and hide "open worksheet".

Result is that user has saved workbook in the required open workbook state then after saving is returned to worksheet they were on when they saved so they could if required continue editing workbook. If i did not cancel original save, each time a save was pressed a user would be sent back to the "open worksheet".

Regards
Steve

PS please try not to get annoyed if I am still not understanding what you are saying. I do appretiate your help and do not want to offend/irratate you or anyone else thet is good enough to give up their time to help me.

do any of your Call macros end with enable events = true?

Did you test if BeforeSave event is being raised after ThisWorkbook.Save call?

Originally Posted by Steve@TRW

Just to clarify as mentioned above, save when closing workbook does work with this BeforeSave event its just when another workbook is open it gets stuck in an eternal loop.

Excel like most programs has ways of recognising infinite loops and getting out of them it is possible that it just can't recognise the loop with two books open rather than it only existing with two open.

Originally Posted by Steve@TRW

Hi

Just had a thought that may resolve one of my issues.

Is there a code that i could put in an Open event in this workbook that makes it open in a new instance of excel (rather than on top of any existing open workbooks)?

Regards
Steve

No unfortunately Application has to be open first as the platform for any macro execution and alternate instances cannot interact. You could write code to open another instance of excel but it would not be able to open a workbook in that instance.

however having said that there may be some shell comand you could write that has a shot at this.

It is possible to reopen file in separate instance and close current version. You may try:
Module:

Please Login or Register  to view this content.

ThisWorkbook:

Please Login or Register  to view this content.

You may try:

Please Login or Register  to view this content.

with simply:

Please Login or Register  to view this content.