Undo all changes since open or last save

I have a spreadsheet that automatically saves itself when you close it (it doesn't prompt the user) as I must make several sheets hidden upon exit. I can't have it prompt the user the normal way as they may chose not to save and of course the sheet state wont then be saved on exit.

My thinking is to setup a basic 'Do you want to save?' Yes/No form upon exit and have the user pick accordingly. If they chose Yes, then it just saves, easy ...If they pick No then it undoes everything modified on the spreadsheet since the last manual 'Save' (They may not have saved it, they may have just opened it and changed a few numbers here and there, both are possible)

Looking at some of the 'undo' code on the internet I'm guessing it's not possible as you have to store every change in order to be able to change it back, right? Perhaps it's possible to somehow use 'SaveAs' and create a temporary copy and somehow revert back to the original one upon exit (if they choose No) ...I don't know I'm just thinking aloud I guess.

If you have any thoughts or suggestions they are very welcome.

Many thanks

Brelin

Does anyone have any thoughts or ideas on how to achieve this?

Many thanks

The thought had crossed my mind about that Marvin. However I can see that people could be going in and out of the file quite a lot (without making changes necessarily) and the result would be quite a few files in there before it's done with. The file is about 1MB so if I can avoid making multiple copies (that don't need to be there) I'd like to.

Originally Posted by snb

Keep it simple: there's no need to hide sheets when closing if you hide the sheets when the workbook will be opened.

The reason I like the idea of hidding the sheets on exit as well is because it turns them all off so if someone was to access the file and disable macros at startup then the all the sheets visible from the last time it was opened would still be there. I use a basic 'login screen' upon opening the spreadsheet and depending on who that is it displays/hides certain sheets. So if the manager was the last to save it and then a standard user opens it with macros disabled he'll see the additonal sheets he shouldn't because the login prompt wouldn't run. I know excel isn't a secure environment as such but I wanted to prevent the average guy from being able to circumvent the system and disabling macros is a very easy one for someone to do.

Originally Posted by MarvinP

Your request is a little different. You are asking how to archive previous versions on save but don't have a specific rule. You might want to save but sometimes not. These save or not, without a set rule is tough to decide how to resolve the problem.

After re-reading your question perhaps this will work. You can just pretend that the workbook has been saved with a command like:

Please Login or Register  to view this content.

It doesn't really save the workbook but when you do close it - it won't ask to be saved.

Is this what you were looking for?

No, I'm not bothered about the archive thing, that was just an idea I had to perhaps get round my problem but creating many copies of the file isn't practical.

This is my exit code:

Please Login or Register  to view this content.

It turns off all the sheets except 'Welcome' and does a force save (no prompt) then close ...exactly what I need to keep the sheets invisible even when the file is closed. However ....and this is the problem ...what's to stop a user opening the file making a complete hash of the numbers and then exiting out expecting to be able to NOT save changes and bam, my code automatically saves the changes. I need the option to be able to undo their work (if they want) and then do my force save ...but I must have my force save at the end.

Making any more sense?

I know it's an old post, but is there is any chance that someone has found a solution for this in the past 5 years ?
I've exactly the same problem !!

Sorry protonleah, I thought it's ok since it's still unsolved and I just want the answer of the original post not a new question. I'm still new here
Thanks for the notice anyway.