Hidden sheet option

I have worksheet with 5 tabs(sheets),
sheets are of individual person, including leaders
One of the tab/sheet i want is for it to be hidden and open only by the leader.
So I have here:

Please Login or Register  to view this content.

this part works, but any user can go and unhide it if they know how. How I can I have it protected?

Hi, EXLent,

maybe like this

Please Login or Register  to view this content.

Any change in view this sheet could only be done via VBA or via the VBE.

Ciao,
Holger

Hi, EXLent,

maybe that is due to the order of commands you have in your Workbook_open? I donīt know the logic behind it but I could imagine to be that code line the first in the procdure and get through the procedure before calling the UserForm.

Ciao,
Holger

Hi, EXLent,

from how I would approach this: check that the given worksheet(s) is/are hidden as first action (I would eventually place that command in the Workbook_BeforeClose-Event). After that I would check if the user who opened the workbook is listed and take these actions accordingly. And if the user is not listed: how shall he enter Data into an UF (and more important: where to store this data)? The command I would use would not be Exit Sub as that would leave the workbook and the UF open but Workbook.Close as that user would have no permission to enter the workbook. And sorry: I donīt see why you need myName (must be a gobal Variable while I used it as a local).

Please Login or Register  to view this content.

Ciao,
Holger

Hi, EXLent,

you could check the status of any sheet prior to taking any action like Selecting (for which reason?)

Please Login or Register  to view this content.

Ciao,
Holger

Hi, EXLent,

Originally Posted by #7

I would eventually place that command in the Workbook_BeforeClose-Event

With the Workbook_Open you keep facing the problem of macros being en-/dosabled while with he Workbook_BeforeSave you would need to make sure that any wanted action from the user has been taken in order to save the workbook (display the wanted worksheet if needed in Workbook_Open and set the status Saved of the workbook to True, in running the procedure check for that status again and hide the sheet if visible and only after that save).

Ciao,
Holger

Hi, EXLent,

never wrote nor meant that - only if no changes to the workbook had been made.

Please Login or Register  to view this content.

See attached workbook - I disabled the code line

Please Login or Register  to view this content.

in order to allow changes to the names being made. This will display the UserForm (will not if closing Workbook os active).

Ciao,
Holger

Hi, EXLent,

Also if possible, can we have user input password without inputting it in the code?

You would need to put it anywhere into code (and may that only be the code to implement a module with this code via VBA).

Like a popup asking for password change.

No problem, I would rely on a userform for that. But you would need to store the password anywhere (as you donīt want it in the code). So maybe a Name or a CustomDocumentProperty, a text file (you would have to make sure that this file is present on any computer the workbook is opened).

it is hidden to everyone other than the leader he still feels not secure.

You may lock the VBA Project and secure it with a password (please keep both a version of the unprotected workbook as well as the password in a secure place). This is far from really being secure but should be good enough to protect from most users.

Ciao,
Holger

Well I got it to work exception to that the sheet shows behind the msgbox.
How can I have it minumized before open with correct password?

Please Login or Register  to view this content.

Hi, EXLent,

what sheet are you talking about to get that routine? Every single userīs one (who is identified by his logging into the computer)? Maybe look at this sample code and see if it helps.

Ciao,
Holger