+ Reply to Thread
Results 1 to 12 of 12

Password Protect Worksheets

  1. #1
    Registered User
    Join Date
    01-29-2009
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    8

    Password Protect Worksheets

    Hello,

    I am seeking a method of password protecting worksheets in a workbook. The users would all start on a sheet that has their names (A1 - A10) next to their names would be where they type in their password (B1 - B10). If their password is correct their assigned worksheet (Worksheet2) would be unlocked for editing.

    Thanks so much!!
    Last edited by excelconsult; 02-06-2009 at 11:55 AM.

  2. #2
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile Re: Password Protect Worksheets

    Good evening excelconsult
    Quote Originally Posted by excelconsult View Post
    I am seeking a method of password protecting worksheets in a workbook. The users would all start on a sheet that has their names (A1 - A10) next to their names would be where they type in their password (B1 - B10). If their password is correct their assigned worksheet (Worksheet2) would be unlocked for editing.
    I have a workbook available the offers something like this. Each user has a username and password which must be entered when the workbook is opened. Once successfully entered the user can only see the tabs that you, as administrator have allowed them to. they can access these tabs on a read-only, or a read-write basis as decided by the administrator. The administrator of course has full access to all tabs in the workbook.

    If you would like a copy of this file, then send me a PM with your e-mail address.

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

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

    Re: Password Protect Worksheets

    here's a link to a an example that has a userform for entering the user password, successful validation unhides the relevant sheet(s)

    PASSWORD FORM4.zip

    When opening the workbook the form shows, pick Roy & use password y.

    The users, passwords etc are stored on a hidden sheet which you can unhide in the VB Editor
    Last edited by royUK; 10-15-2010 at 01:50 AM.
    Hope that helps.

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

    Free DataBaseForm example

  4. #4
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Password Protect Worksheets

    hi ExcelConsult,

    There is a caveat to worksheet protection (in fact some would say, to any excel protection). If the users know Excel/VBA well they can probably bypass the passwords etc. Note, I say this without having looked at either Dominic or Roy's attachments.

    Here is a link to John W's site where he discusses some of excel's security:
    http://spreadsheetpage.com/index.php...ction_faq1/#P1

    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

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

    Re: Password Protect Worksheets

    I agree Excel is not secure, if MS Windows, etc can be pirated anything can.

    However, protecting the VBA Project & hiding the sheets as very hidden as I do should deter most users.

  6. #6
    Registered User
    Join Date
    01-29-2009
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Password Protect Worksheets

    All,

    Thank you for the suggestions. Roy I found your solution to work great...can you tell me how to unlock the VB to show the page that houses the passwords? Other than that I think I will mark this one closed.

    Thanks

  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 Protect Worksheets

    Just go ito the VB Editor and click on the sheet called Hidden. Change it's Visible property to xlSheetVisible

  8. #8
    Registered User
    Join Date
    01-29-2009
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Password Protect Worksheets

    Thank you so much,

    This worked out great...I will mark the thread as SOLVED.

    Thanks again

  9. #9
    Registered User
    Join Date
    01-29-2009
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Password Protect Worksheets

    Roy,

    Is there a password in the example say for an administrator that can unlock all the worksheets at once? If so how would I modify the code?

    Thanks again

  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 Protect Worksheets

    I've added this
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    02-08-2009
    Location
    Bridgwater, UK
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Password Protect Worksheets

    After hunting around, I found this topic and it is roughly what I was after. However, I have a couple of questions.

    In the code, there is;

    Please Login or Register  to view this content.
    I have tried setting my own range into this with;

    Please Login or Register  to view this content.
    but this does not populate the user list on the form.

    Also, on the line;

    Please Login or Register  to view this content.
    What does the xlValues mean?

    TIA

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

    Re: Password Protect Worksheets

    Fox111
    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

+ 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