+ Reply to Thread
Results 1 to 22 of 22

Password Protecting Worksheet from Viewing

  1. #1
    Registered User
    Join Date
    10-14-2010
    Location
    WI
    MS-Off Ver
    Excel 2007
    Posts
    77

    Password Protecting Worksheet from Viewing

    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.
    Last edited by TWent; 03-01-2011 at 12:49 PM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Password Protecting Worksheet from Viewing

    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
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    02-27-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    40

    Re: Password Protecting Worksheet from Viewing

    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.
    Attached Files Attached Files
    Last edited by MrExcelPro; 02-28-2011 at 05:01 PM.
    Regards
    MrExcelPro

  4. #4
    Registered User
    Join Date
    10-14-2010
    Location
    WI
    MS-Off Ver
    Excel 2007
    Posts
    77

    Re: Password Protecting Worksheet from Viewing

    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.

  5. #5
    Registered User
    Join Date
    10-14-2010
    Location
    WI
    MS-Off Ver
    Excel 2007
    Posts
    77

    Re: Password Protecting Worksheet from Viewing

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

  6. #6
    Registered User
    Join Date
    02-27-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    40

    Re: Password Protecting Worksheet from Viewing

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

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Password Protecting Worksheet from Viewing

    This example gives access to different sheets for each user

    PASSWORD FORM4.zip

  8. #8
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Re: Password Protecting Worksheet from Viewing

    Amazing information! Such a wonderful excel workbook attachment.

    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??

  9. #9
    Registered User
    Join Date
    10-14-2010
    Location
    WI
    MS-Off Ver
    Excel 2007
    Posts
    77

    Re: Password Protecting Worksheet from Viewing

    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.

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Password Protecting Worksheet from Viewing

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

  11. #11
    Registered User
    Join Date
    10-14-2010
    Location
    WI
    MS-Off Ver
    Excel 2007
    Posts
    77

    Re: Password Protecting Worksheet from Viewing

    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.

  12. #12
    Registered User
    Join Date
    07-12-2013
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2013
    Posts
    1

    Re: Password Protecting Worksheet from Viewing

    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!

  13. #13
    Registered User
    Join Date
    09-14-2012
    Location
    Pune, India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Password Protecting Worksheet from Viewing

    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.


    Code

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

    Please Login or Register  to view this content.
    ---------------
    Last edited by arlu1201; 04-14-2014 at 01:40 AM. Reason: Use code tags.

  14. #14
    Registered User
    Join Date
    03-26-2014
    Location
    New York, New York
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Password Protecting Worksheet from Viewing

    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
    Last edited by mcurry101; 03-26-2014 at 03:48 PM. Reason: forgot line

  15. #15
    Registered User
    Join Date
    04-13-2014
    Location
    Ahvaz, Iran
    MS-Off Ver
    Excel 2013
    Posts
    1

    Re: Password Protecting Worksheet from Viewing

    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.

  16. #16
    Registered User
    Join Date
    01-26-2016
    Location
    Dover, DE
    MS-Off Ver
    2010
    Posts
    1

    Re: Password Protecting Worksheet from Viewing

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

  17. #17
    Registered User
    Join Date
    07-13-2016
    Location
    Iran
    MS-Off Ver
    2016
    Posts
    1

    Re: Password Protecting Worksheet from Viewing

    please update links :/

  18. #18
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Password Protecting Worksheet from Viewing

    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 !

  19. #19
    Registered User
    Join Date
    10-05-2016
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    2

    Re: Password Protecting Worksheet from Viewing

    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!

  20. #20
    Registered User
    Join Date
    11-11-2015
    Location
    Colorado
    MS-Off Ver
    Mac 2011
    Posts
    1

    Re: Password Protecting Worksheet from Viewing

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

  21. #21
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Password Protecting Worksheet from Viewing

    thescream80 welcome to the forum

    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.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  22. #22
    Registered User
    Join Date
    06-19-2019
    Location
    United States
    MS-Off Ver
    office 365
    Posts
    1

    Re: Password Protecting Worksheet from Viewing

    this is very useful information. worked like a charm

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1