macro to undo user changes since last save

[nguye569]

Hi I would like a macro to undo all user changes since the last time it was saved.
Basically, I have a worksheet that requires users to enable macros before they use it because it can still be used without enabling macros and can give incorrect results if done so.

All sheets are hidden and unhidden when user enables macros and clicks button.

On close, if worksheet is detected to have been saved already. then macro will rehide all sheets except for 1, saves itself, and closes.

If worksheet has not been saved, a form shows up asking if user wants to save.
Click yes, then the macro hides all sheets except for 1, saves itself, and closes.

Click no is where I have a problem.

In the case where the user has inputted data already, clicked save, and inputted more data - I want the no button to undo all changes leading up to the last save, rehide all sheets except for 1, save itself, and close.

Is this possible? Any help would be appreciated, thanks!

[royUK]

How can they use the workbook if all sheets are hidden until macros are enabled?

[MarvinP]

Hi nguye569,

Would a close of the workbook without saving changes since it was last saved work?

There is a VBA call to do just that. Read: http://support.microsoft.com/kb/213428

[nguye569]

@royUK - the first sheet is visible and there is a button to unlock all the hidden sheets. That button can be clicked once macros are enabled.

@MarvinP - I have seen that already. It helped me a little bit, but there is a loophole.
If the user has unlocked sheets to edit data and the user clicks save in the middle of working and adds more data. If the person chooses to not save the most recent information, and I supress the save changes dialog box then the next time the sheet is open, the sheets will be all visible again. However, I need the sheet to always rehide all the sheets except for sheet 1 whether the use decides to save changes or not save changes.

so the steps would do this.
1. click No when asked to save changes
2. Undo all user changes since last save
3. make sheet 1 visible
4. hide all other sheets
5. Save
6. close

Step 2 is what I can't find the code to do

[royUK]

Basically, I have a worksheet that requires users to enable macros before they use it because it can still be used without enabling macros and can give incorrect results if done so.

Your reply to my question doesn't make sense

[MarvinP]

To Undo all users changes from last time it was saved:

Why can't you simply open it up again without saving any changes?

[nguye569]

To Undo all users changes from last time it was saved:

Why can't you simply open it up again without saving any changes?

I have a beforeclose event that hides all worksheets except for sheet 1 when the workbook is closed. Sheet 1 has only a button on it to unhide all the worksheets (I did this to prevent the user from using the workbook unless macros are enabled).

Because of the beforeclose event, there will be a prompt to ask the user if they want to save the changes. If the user clicks yes then there are no problems.

If the user clicks no, and they saved their work sometime after opening the file initially, then the next time they open the file all worksheets are still visible (something I don't want to happen).

[royUK]

In the Before_Close event you should run the code to hide the sheets, then save the workbook. If the user has saved the workbook previously you can't undo anything before that