+ Reply to Thread
Results 1 to 16 of 16

Password to allow viewing of a certain sheet

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    Urbana, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Password to allow viewing of a certain sheet

    Greetings,

    I know how to protect sheets in a given workbook with a password but I would like to know if there is way to make it so that a person is required a password to even view a certain sheet in a workbook.

    Any help would be greatly appreciated.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,163

    Re: Password to allow viewing of a certain sheet

    Not without VBA
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    06-03-2013
    Location
    Urbana, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Password to allow viewing of a certain sheet

    Is there a sample code that can be provided since I have very little VBA programming experience?

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,163

    Re: Password to allow viewing of a certain sheet

    Right click on the sheet tab >> View Code >> paste code in sheet module

    Please Login or Register  to view this content.
    Now when the user selects the sheet tab they will be prompted for a password which must equal "temp"

  5. #5
    Registered User
    Join Date
    06-03-2013
    Location
    Urbana, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Password to allow viewing of a certain sheet

    alright cool. thank you. is the code able to prevent viewing the sheet until a password is prompted because right now the code seems to allow the sheet to be viewed then followed by a prompt for a password

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,163

    Re: Password to allow viewing of a certain sheet

    I don't have a good working sample in front of me here, but maybe I can get one later.

    Just a little clarification on what you are trying to achieve though.

    Once the user picks the sheet they want to view and they enter the correct password, what is to happen after they are done or better yet, when they close the file?

  7. #7
    Registered User
    Join Date
    06-03-2013
    Location
    Urbana, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Password to allow viewing of a certain sheet

    If a user leaves and goes to another sheet I would like the protected sheet to remain unlocked or viewable until they completely close the workbook in which case they would need to enter a password again when they open up the workbook and select the sheet again. Im not pressed for time so when ever you get around to it, I would greatly appreciate it.

  8. #8
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,099

    Re: Password to allow viewing of a certain sheet

    Hi, Paragoomba,

    maybe you can explain if we are talking about one worksheet that one person isn´t allowed to see or if we are talking about several sheets of which some sheets may only be seen by certified people? This would mean a hidden worksheet in the workbook with a lookup table who is going to see which sheets and a UserForm for both the name of the user and password as well as either a Listbox or ComboBox listing the sheets for that special user once the password suits the username entered.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  9. #9
    Registered User
    Join Date
    06-03-2013
    Location
    Urbana, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Password to allow viewing of a certain sheet

    There will be several sheets that will need to require a password in which anybody who has access to the password will be able to view. I cannot attach my workbook since the information is confidential.

  10. #10
    Registered User
    Join Date
    11-27-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Password to allow viewing of a certain sheet

    Try this. I think this is what you are trying to accomplish.

    password tab.xlsm

  11. #11
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,099

    Re: Password to allow viewing of a certain sheet

    Hi, Paragoomba,

    maybe like this.

    For hiding the sheets in ThisWorkbook:
    Please Login or Register  to view this content.
    For bringing up the UserForm behind Sheet Master:
    Please Login or Register  to view this content.
    Behind the UserForm frmAccess:
    Please Login or Register  to view this content.
    Default button when leaving the TextBox is Cancel.

    Ciao,
    Holger
    Attached Files Attached Files

  12. #12
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Password to allow viewing of a certain sheet

    The trick I used to keep people from seeing ANY part of the sheet while the password prompt is onscreen is to send them to a blank part of the sheet while this is going on.

    This should give you something to play with:
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  13. #13
    Registered User
    Join Date
    06-03-2013
    Location
    Urbana, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Password to allow viewing of a certain sheet

    Thanks guys. Youve been a great help.

    JBeaucaire,

    Are you able to make that code to where you don't have to re-enter the password every time you click on the sheet? Only when you close and re-open the file.

  14. #14
    Registered User
    Join Date
    06-03-2013
    Location
    Urbana, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Password to allow viewing of a certain sheet

    post deleted
    Last edited by Paragoomba; 07-08-2013 at 09:29 AM.

  15. #15
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Password to allow viewing of a certain sheet

    Like so, just edit the password at the top:

    Please Login or Register  to view this content.


    As for forcing macros to be enabled, that's a whole different topic. You can Google that question (Excel VBA force macros to be enabled) or start a new thread.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  16. #16
    Registered User
    Join Date
    06-03-2013
    Location
    Urbana, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Password to allow viewing of a certain sheet

    Excellent, thank you very much,

    I figured out how to automatically enable macros as well.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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