"Undo" Workbook BeforeClose event if saving is canceld

In my latest creation I needed a way to basically force the user to enable macros so that everything would work correctly - I thought I had it perfect (use VBA to unhide the sheets after being enabled, with the only visible sheet to start being a note about enabling macros) using the Workbook Open and BeforeClose event (the latter to then re-hide the sheets so only the first sheet was visible when opened again).

I since noticed, however, that the BeforeClose event fires before the "Do you want to save this sheet" box is brought up. Seemingly minor, since in most cases the user is going to click "Yes". However there may be times when "No" is clicked instead, after saving the workbook manually in the usable view. After noticing that, I also realized that there's another possible fault here - if the user clicks "Cancel" then the hiding is already done, they can't actually get back in anyway and may not realize why...

Am I SOL in the latter examples? I thought about doing BeforeSave instead but then if the user saves in the middle of using the book they're lost as well. (unless there is a way to detect if the save button was pushed by the user or if it is because they are attempting to close the workbook? - I thought maybe SaveAsUI was this, but alas, it does not appear to be)


Is there any way to do what I want to do? Basically I always want the workbook to open to the Special Note sheet and not have the other sheets available until macros are enabled. Ideas?

Hi,

You should be hiding the sheets in the BeforeSave event handler, not in the BeforeClose event handler. There are two reasons for this:

(1) If you use the BeforeClose event, if the current user ("User1") saves their progress and continues to use the workbook, if another person decides to open the workbook ("User2"), they would be able to open it with macros disabled and see all of the sheets. This is because all of the sheets were visible when User1 saved the workbook - they won't be hidden again until User1 closes the workbook.

(2) If you use the BeforeClose event, your users have no way to close the workbook without saving. What if they have made some changes and then decided that they don't want to save them?


These two issues make the BeforeSave event handler a better choice. When the user saves the workbook, you need to hide the relevant sheets, save the workbook and then unhide the relevant sheets again so that the user can continue to use them.

Assuming that the name of the sheet you want visible if macros are disabled is "Special Note", the code would be something like this:

ThisWorkbook class module

Please Login or Register  to view this content.

Note that the set up I've posted does not give the user the option to SaveAs.

Sometimes I miss the logical things... Hide/Save/Unhide makes total sense.

Thanks!

EDIT
OK, so I responded when I realized the "duh" moment of the logistics of doing this...
However, now that I've put your code in (with some minor changes for sheet names, etc, also tried adding in a catch to allow Save As, however it still does this even when that area is completely commented out) it saves when I save using the "save" button, but if I try to use the red x after making changes I get stuck in a loop of "Do you want to save..." by clicking yes. (If I click No it will close and was saved, but the fact that it shows the window again is not user friendly)

I've put in breakpoints at various points, including the "Exit Sub" line and checked the variables, they all look right at the end (Saved is in fact true, cancel is in fact true, etc...) I can't figure out why it is looping when I try to use the close button.

Hi - please can you post your revised code?