Password protect a single worksheet within a workbook from unauthorized view

I have a workbook with multiple sheets that reference data between them. I need one worksheet viewable only to select users. This workbook is used regularly.
In researching, I've identified how to protect a worksheet from unauthorized datachange (though still viewable), and how to password protect an entire workbook from unauthorized access and viewing. Is it possible to password protect a single worksheet within a workbook, so that only authorized users (password holders) can view it?

You can hide the sheet and then password protect the workbook structure. Then users will need to unprotect the structure to unhide the sheet. Just train your users to hide and protect when done. Other than that a macro solution will be needed.

You could also hide the rows/columns then protect the sheet. Then the user would need to unprotect the sheet to unhide the rows/columns. To make it easier on the user, use Grouping.

Thank you for the feedback. I did have success using a macro to hide and unhide the worksheet and then apply a password protect to the worksheet, as mentioned by Whizbang. However, it hinges on superusers remembering to hide the worksheet, prior to saving. If they fail, the worksheet will be visible. I'm leaning now towards placing the two groups of data in separate workbooks and linking one to the other, one read-only and the password protected.
The only other option I've been able to glean from research is the possibility of a third spreadsheet containing user names and then somehow linking those credentials to passwords so that users will see different worksheets depending on who accesses the workbook. Is anyone familiar with that technique? I found one post in ExcelForum that spoke to it, but the sample spreadsheet seemed blank to me.

Use

Please Login or Register  to view this content.

To get the current user. Then use something like:

Please Login or Register  to view this content.

Thanks, Whizbang, but I'm not sure how to implement this. I've tried adding the larger code as Visual Basic in the "This Workbook" window, which populates the choices of workbook and open in the fields above. Where does smaller go?

The smaller bit ( Environ("Username") ) is already in the larger bit. I was just highlighing the code used to fetch the current user name.

How this works is that it searches column A in a sheet called "Userlist" for the username. If found, it returns the corresponding column B value. If the corresponding value is "Admin", then unhide the "Hidden Sheet".

This will need to be modified to suit your needs. Change "UserList" to whatever sheet contains your list of users and their roles. Or create a sheet "Userlist" to hold this data. Change "Admin" to whatever is appropriate. "Hidden Sheet" would simply be whatever sheet you want hidden except for certain users.

If you post a sample workbook , I can give you a more customized answer.

Whizbang, you'll find an attached workbook. Trying to understand this on my own, I added the code you mentioned below to Visual Basic for the Calculation spreadsheet, but I'm not understanding how to add user names for the code to search through. All I really need is two user types: One user group that will view all worksheets and one that view only the Schedule worksheet.

Please see the attached.

The code goes into the ThisWorkbook module. This is because we want this code to run when the workbook is opened.

I created a sheet called "UserList". This sheet has two columns, Username and "View All". Enter the username in column A (exactness is important), then TRUE or FALSE in "View All". You really only need to add users that will view all. So, TRUE will only ever need to be entered in column B. If the user is not in the list, then they cannot see the calculation sheet anyway. Since only one value is expected in col B, we could even eliminate this column and just search col A for a match. I chose not to do this for you because I wanted to show that you could have multiple permission options. Just add a column, add a test in your code, and you are all set.

If you will only ever need this one permission, then simply delete column b in Userlist and modify your code to this:

Please Login or Register  to view this content.

I also hid the Calculation and Userlist sheets. This is so that they will remain hidden if the user is not found in the list or if they do not enable macros.

Oh, and you may want to insert this code into your ThisWorkbook module as well. This is to re-hide the sheets upon close.

Please Login or Register  to view this content.

Oops. Change all occurences of .Hidden to .Visible. Then swap your TRUE and FALSE values.

Code should be:

Please Login or Register  to view this content.

Whizbang, I tried the code above, and I know it's working, because I locked myself out. When I open the file, I only see one spreadsheet. Perfect.
However, now I'm not able to figure out how to get it to allow me to see the hidden sheets. I was expecting it to ask for a user name (like a logon credential) when I opened the file and check that against the usernames in column A. But it simply opens the file with the hidden worksheets.
How does it know the user name of the person opening the file to check it against the user names in column A? Is it the default user name for the installed version of Excel?

It gets the username from Windows. Here is a link that gives a good description of the Environ() function.
http://blogs.officezealot.com/charle...2/10/3574.aspx

To add yourself to the list, simply right-click on any sheet name and select "Unhide" and unhide the Userlist sheet. Then add yourself to the list in column A.

To help prevent other users from adding themselves, you would want to protect the workbook structure in the Review tab. Then you would need to modify your code to include locking and unlocking the workbook structure.

Please Login or Register  to view this content.