# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Password Protecting Worksheet from Viewing

## TWent

Hello I am our companies excel workbook creator and I am a self taught newbie to the excel world. VBA is not yet a strong point for me however I get along fine using the basic formulas and features made available to me from the Excel 07 software. I have come to a point where I need to insert some code to get the end result I want. Trust me I tried to find a way to protect this sheet from view but nothing was good enough to not just be simply undone.

I have created a quota records sheet that takes nine different employees work and totals it per day, month and then adds it to a line graph for visual competitiveness against the other workers. The last sheet I have is only for the Managers and Assistant Managers eyes. How can I password protect this sheet from view unless a correct password is entered?

*1. Password Protect (Sheet 11) "Company Totals"
2. Three Attempts allowed
3. After three attempts have failed it sends you back the the sheet you were previously viewing.
4. Disable the ability for this sheet to be the one viewed first when first opening the file.
5. Only need to enter the password once per session to view this sheet.
*
If you need more information to understand the solution I want then please ask.

PS: Possible conflicts, Workbook is shared and saved in excel 97-2003 format once editing is finished. This makes the file compatible to all employees using it as we have not yet upgraded everyone to Excel 2007.

----------


## royUK

Hide the sheet, then password protect the workbook. You need to protect the workbook's structure. Select Tools -> Protection -> Protect Workbook. In the Protect Workbook dialog box, make sure that the Structure checkbox is checked. If you specify a password, that password will be required to unprotect the workbook.

When a workbook's structure is protected, the user may not:

Add a sheet
Delete a sheet
Hide a sheet
Unhide a sheet
Rename a sheet
Move a sheet

----------


## MrExcelPro

Hi,

You could use the veryhidden method in VBA. If a user hides a sheet using the top menu they can simply unhide it themselves. If the veryhidden command is used in VBA then you cannot unhide it. 

You could create a list of verified users (people who can view it) in a column in the VeryHidden sheet. On opening the workbook you could check the users name and if its on the list automatically unhide the sheet removing the use of using a password that anyone may get hold of.

Anyway, the attached spreadsheet has 3 sheets

Visible
NormalHidden
VeryHidden

Use the command button on the Visible page to hide / unhide the veryhidden sheet. Try and unhide it using a standard excel menu and you will have no luck. 

The VBA code below is what I have inserted in sheet1




```
Please Login or Register  to view this content.
```

----------


## TWent

Thank you for the speedy response, however I have looked at the option you have stated. I may not have been clear when explaining the workbook. Each employee enters in their own personal data to the sheet with their name. Then the number is totaled for all employees along with the number of each company's total completed. It is the way we can tell if someone is fibbing their work for the day. Everyone's totals are visible on the "Company Totals" sheet. 

This workbook is shared and needs to be visible/editable by all employees entering in their data at the conclusion of each day.

I want to make the last sheet only visible if you enter the correct password. Protecting the entire workbook would mean only myself or the manager would be able to enter all the employees data.

----------


## TWent

Just a simple password to view the "Company Totals" sheet is all I want.

----------


## MrExcelPro

Please see the attached file. This is as simple as it gets




```
Please Login or Register  to view this content.
```

----------


## royUK

This example gives access to different sheets for each user

PASSWORD FORM4.zip

----------


## SunOffice

*Amazing information! Such a wonderful excel workbook attachment.*  :Smilie: 

It works in a very well manner, but the workbook doesn't close if i put any wrong password in the three chances. Yes! I am unable to see the rest of the worksheet but I hope the workbook should be closed for working.

What if he tries to re-open the workbook and looking for the next three fresh chances to log-in on?? Is there possible that we can stop him to re-login for the next 2 hours when he fails for the 1st time??

----------


## TWent

This is the type of thing I am looking for. I have already tried this code, I was unsuccessful in making it work properly. If you could please take a look and maybe help fix this up a little. When I insert this code nothing happens.





```
Please Login or Register  to view this content.
```

----------


## royUK

Protecting the workbook is the best option. Any code that you use will only work if the user enables macros.

----------


## TWent

Thanks you all for the great options. I think using a reference to the main workbook is my only option. I will remove the "Company Totals" or sheet 11 from the main work book and just add it to its own workbook to make a reference. I have never done this but it seems to be the best option.

----------


## jaraak

This example is EXACTLY what I have been looking for.  However, I am getting stuck on a couple of things when trying to alter it.  I would like to change the User Name & PW to manage the worksheet (to show all of the sheets / data).  I have been unsuccessful so far figuring out how to do this.  Could someone help me out with this?

Thanks!

----------


## igovardhanboga

Hi Roy,

i have checked the zip file that you have uploaded and its really good one and i am looking for the same.
this is really helpful to me but wanted to check one thing.
this is just a normal excel file not macro enabled and when i checked macro seen found only below code.

pelase advise--
1. i want to add more sheets and more users
2. is it possible to assign more users to one sheet
3. is the only below code that is used in this file


BRgds,
Govardhan.
 :Smilie: 

Code

--------------



```
Please Login or Register  to view this content.
```






```
Please Login or Register  to view this content.
```


---------------

----------


## mcurry101

Just diving into this.  Could be a really big help.  Forgive me if I am missing the obvious, but how would "Jack" log in?  i.e. what is the password he should enter? I am not having any success with "secret", "x", "Dept1", so maybe I am doing something wrong?  Also, where and how would someone change, add, or delete users and which sheets they can view?  Thanks so much!!

This is in relation to the file "passwordForm4.zip" posted by royUK on 3/1/11 (thanks so much royUK!).  Any help appreciated...

Matt

----------


## sadeghhajarpour

When you want to log in as manager you need to enter "Manager" as username and "secret" as the password, if you want to enter as user jack who can see the sheet Dept1, you have to put jack as username and x as password. After entering username and password, you need to press Validate button and your appropriate sheet will open.

----------


## Dartimus

This link comes up as no longer valid. Do you still have this example, and if so, could you share it with me?

----------


## Dr.Fox

please update links :/

----------


## Logit

Roy:

I was reviewing your posts regarding the password protected sheet options. I would be very appreciative if you could forward a copy of the
PASSWORD FORM4.ZIP file that was previously available. Thank you so much !

----------


## dr_unik

Hi Roy!

Looks like your file is something that I might find useful as well. Are you able to attach it to this chain of email please. Thank you!

----------


## thescream80

Would love how to do this. Trying to lock different sheets that can only be viewed via password

----------


## FDibbins

thescream80 welcome to the forum  :Smilie: 

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## topdog63143

this is very useful information.  worked like a charm

----------

