Closing WB with a timer no changing sheets to very hidden

[Nitro2481]

Hi Guys,

I have this code in my "This workbook"

Please Login or Register  to view this content.

This changes all sheets from very hidden to visible and when closing back to very hidden again. This stops sheets from being visible if there is a macro error when the sheet is starting up. This works perfect.

I also have a timer set up that if there is no activity in 10 minutes the workbook auto closes. When this happens the sheets do not change to very hidden therefore and macro errors going forward display all sheets which I really do not want.

Please Login or Register  to view this content.

I'm assuming I also need a "change all sheets to very hidden on close" code in the timer section but not sure how I would go about that.

I have manually set all sheets bar Splash to very hidden. If you close this with the x on top and try to open the spreadsheet with macros on high you get a warning with only the splash sheet visible. If the timer runs out (for this example I have set the timer at 1 min)and the sheet closes and you reopen with macros set to high you get the same warning message but all sheets are visible and accessible. Although I am aware that Excel is not secure I am more worried about someone stumbling across this information as opposed to being malicious. I have attached the spreadsheet. If you select user name: John and password: Test

Thank you

[Kevin#]

Insert this in the code before file saves and closes.
There must be at least one sheet visible - assumes that "Splash" is that sheet. All other sheets are hidden.

Please Login or Register  to view this content.

To unhide all sheets

Please Login or Register  to view this content.

[shg]

Kevin, you need to set Splash visible before running that first loop, for the very reason you mentioned.

[Kevin#]

@shg is correct. I was assuming that it was visible from the write up.
To ensure that is the case, insert this line as the first line.

Please Login or Register  to view this content.

[Nitro2481]

Thanks for the reply guys,

I'm not great at code. I've been helped with everything I've done so far. Where do I put this extra code. I assumed it was in the timer module which I did below but this didn't rehide the sheets when the timer ran out.

Please Login or Register  to view this content.

[Nitro2481]

I have also tried it in "This Workbook"section but again didn't work although I expect I've put it in wrong

Please Login or Register  to view this content.

[Kevin#]

You appear to have put the code in the correct place the first time.

Here is a dummy file so that you can see the code working.
When you open the file {ctrl} {shift} + T unhides all the sheets
then {ctrl} + T hides them one by one with a message box so that you can see them close, ending up on "Splash" and then vba Saves and Close

To work out why it is not working for you:
Suggest you put a couple of message boxes (where indicated below) in your code so that you can see where it is not doing what is should be doing. The code may not be following the path you think it is. Hopefully these will give you a clue.
(include the MsgBox lines as they are)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Report back if working or not

[Nitro2481]

Hi Kevin,

Thanks a mil for your patience. I think I have it working with your first part of code.

Please Login or Register  to view this content.

The second part seems to be causing errors. Do I require this second part for any reason? Sheets are already programmed to unhide depending on who logs in. Your bit ensures all sheets return to hidden when timer runs out. I just want to make sure I'm not leaving myself open by leaving the second part out.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Kevin#]

Put the second part in another standalone subroutine - it is there to allow you to unhide all sheets

[Nitro2481]

Hi Kevin,

Have that working perfectly. Thanks a million for your help.

[Kevin#]

Glad you got it working.
You puzzled out where the problem was for yourself - that's the only way to gain a real understanding of VBA.
Thanks for the reps