Hide and restrict 1 book in a worksheet

[SelinaR]

Hiya

looking to hide a 1 book/tab in a large worksheet without complicated VB.

Scenario: Multiple staff have access to workbook however only some staff are to have access to one of the tabs within it (must be hidden and with a password to unhide)

* Issue:
> Moving the "hidden" tab to another book with a hyperlink that requires password to open is no viable as the hidden tab is hyperlinked to multiple other sheets within the book and could corrupt with multiple users.
> Option to hide tab & password the book as read only was an option, however if staff open with password to have full access and unhide the tab to view/edit then forget to rehide before saving again, exposes data on tab to staff who must not see it.

Any other ideas?

 

[jorismoerings]

Assuming the workbook needs to be worked in but the structure of it is fixed, so no new sheets, etc.

Following that:

1. I would hide the tab and create password protection on the workbook structure
2. give staff "with a need to know" the password to unlock and unhide the "hidden" sheet
3. create a small VBA workbook event which:
   1. runs on closure of the workbook; and
   2. determines if the sheet, which should be hidden on close of the workbook, is visible; and
      1. hides the sheet; and
      2. ask for the password to protect the workbook
4. finalize the closing of the workbook

 

[sastoka]

Hi there,

I used this a while ago.

Have look and let know if it works for you

Secured workbook

Regards,

 

[Stiuart_W]

To be honest. The way you are talking that there is data that "staff who must not see it." and you are asking for code that is "not complicated" then an Excel solution is probably not for you. I don't know the reasons why that data should be hidden but you should be aware that Excel security, in and off itself, is NOT of the highest rank and generally can be defeated by those willing to try. Whilst there are probably many elegant solutions using VBA there are many scenarios where the code fails to protect the data in the way you want.

I would certainly suggest that if the data needs to be hidden for regulatory, legal or both reasons that you explore bespoke software.

 

[liveinhope]

To be honest. The way you are talking that there is data that "staff who must not see it." and you are asking for code that is "not complicated" then an Excel solution is probably not for you. I don't know the reasons why that data should be hidden but you should be aware that Excel security, in and off itself, is NOT of the highest rank and generally can be defeated by those willing to try. Whilst there are probably many elegant solutions using VBA there are many scenarios where the code fails to protect the data in the way you want.

I would certainly suggest that if the data needs to be hidden for regulatory, legal or both reasons that you explore bespoke software.

It doesn't even have to b someone deliberately trying to bypass the security. Say you have hidden the "secret" sheet, protected the workbook with a password and given the password to Alice and Ben ; the only 2 users besides yourself who are allowed to unhide and view/amend the sheet.

Alice opens the workbook , unprotects by entering the password and unhides the sheet. She makes the necessary changes and hides the sheet again. When she protects the workbook again , she has to enter the password again .
But instead of entering "your" password she just clicks OK . Now anybody can unprotect the workbook.

(Maybe) even worse, Alice is a bit distracted when she enters the password to re-protect the sheet. Instead of entering the "correct" password she enters and confirms the password for another workbook she has access to.

Similar thing happened very recently where a good friend works .. she rang and asked if I knew any way to get back into the hidden sheet. Sorry .. no can help
Ben opens the workbook and finds the password he has been given doesn't work. Murphy's laws Alice is by now on the first day of a six week overseas holiday

 

[AkaTrouble]

although the above replies are all very sound and excel is designed to be usable rather than hidden, we tend to hide stuff from the user to make things less complicated or to prevent accidental changes to formulas etc

is a couple of options i have tried in different projects

i have set sheet protected from changes changed the font colour to white so the text can not be seen but can be referred to for calculations etc

also i have created global variables in a module and then passworded the VBA rather than anything on the sheet

but as said before most protections in excel are not extremely difficult to circumvent

i would say if you cant trust certain workbook users then create user forms and give them zero access to the workbook or its structure but this option is not simple


hope Alice is having a nice time