Page 1 of 5 123 ... LastLast
Results 1 to 10 of 43

Hide a sheet and Password protect it

This is a discussion on Hide a sheet and Password protect it within the Excel Questions forums, part of the Question Forums category; I want to hide a sheet and I can do that. However, what I want to do it that when ...

  1. #1
    New Member
    Join Date
    Apr 2004
    Posts
    3

    Default Hide a sheet and Password protect it

    I want to hide a sheet and I can do that. However, what I want to do it that when the user wants to view the sheet I just hid, he has to unhide and then enter a password. Right now tthere is no password.

  2. #2
    Board Regular
    Join Date
    Jun 2003
    Location
    Houston
    Posts
    287

    Default

    The only way I know to do this is with a macro. Hide the sheet as xlVeryHidden. That way the user can't see it in the sheets collection to unhide. To view the sheet they would have to execute a macro that would ask for a password (hardcoded) or even perhaps held on another 'very hidden' sheet. If they enter the correct password, have the macro unhide the sheet.


    Of course you could protect the Workbook with a password, but then that will likely prevent them from doing a lot of other things you want them to do. Then again, maybe not, but it won't re-hide and re-protect the sheet when they are done.
    Brad

  3. #3
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,245

    Default Re: Hide a sheet and Password protect it

    Welcome to the Board!

    This is one way:

    Private Sub Worksheet_Activate()
    ****pword = InputBox("Please Enter a Password", "Unhide Sheets")
    ****If pword <> "pword" Then ActiveSheet.Visible = False
    End Sub


    The code goes in the module specific to the sheet that is hidden. When the user selects Format-->Sheet-->Unhide, the sheet will be unhidden, but they will be asxked for a pasxsword. If they get it wrong, the sheet will be rehidden.

    Hope that helps,

    Smitty

  4. #4
    New Member
    Join Date
    Feb 2009
    Posts
    1

    Default Re: Hide a sheet and Password protect it

    One may not want the password to be found directly in the sheet code. Here comes an improved version of Smitty:

    Private Sub Worksheet_Activate()
    Dim pswrd As String
    Dim pword As String
    Call password_request(pswrd)
    pword = InputBox("Please Enter a Password", "Unhide Sheets")
    If pword <> pswrd Then ActiveSheet.Visible = False
    End Sub

    Sub password_request(pswrd As String)
    Dim iFileNo As Integer
    iFileNo = FreeFile
    'open the file for reading
    Open "C:\UPC\excel_password.txt" For Input As #iFileNo

    ' Read password
    Input #iFileNo, pswrd

    'close the file
    Close #iFileNo
    End Sub

  5. #5
    New Member
    Join Date
    Feb 2006
    Posts
    3

    Default Re: Hide a sheet and Password protect it

    Hi,

    I found this code on a previous post:

    Private Sub Worksheet_Activate()
    ****pword = InputBox("Please Enter a Password", "Unhide Sheets")
    ****If pword <> "pword" Then ActiveSheet.Visible = False
    End Sub

    but when I paste it into the sheet in VBA I get the following error:

    Compile Error
    Syntax Error

    And it highlights this portion of the code:
    ("Please Enter a Password", "Unhide Sheets")

    Sorry for my ignorance, I am not too familiar with VBA, but where does the actual password go in the code? Right now, I do not have a password in the code...is that why I am getting this error?

    Any help is much appreciated!!

  6. #6
    Board Regular
    Join Date
    Apr 2007
    Posts
    338

    Default Re: Hide a sheet and Password protect it

    Delete the asterisks if you copied those into the code and replace them with a tab or space... I think they were meant as visual placeholders...

  7. #7
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,916

    Default Re: Hide a sheet and Password protect it

    Since all these approaches require VB, using VeryHidden would probably be easiest.
    Also, I don't think the concern about the user seeing the password (if its hard coded into the VB routine) is not really valid. If the user can get into the VB Editor and knows VBA well enough to interpret the password correctly, any security is already blown.

    The situation where the user is
    1) able to get a password from a VBA module and
    2) unable to break the security without that password
    is a very rare.

  8. #8
    New Member
    Join Date
    Sep 2009
    Posts
    17

    Default Re: Hide a sheet and Password protect it

    I have workbook that un-hides sheets when the correct password is entered... This is great but is there anyway to hide the password when it is entered?

    Like instead of having "password" display when entered, have "********" display?

    Thanks in advance.

    GT

  9. #9
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Hide a sheet and Password protect it

    Quote Originally Posted by gstullo View Post
    I have workbook that un-hides sheets when the correct password is entered... This is great but is there anyway to hide the password when it is entered?

    Like instead of having "password" display when entered, have "********" display?

    Thanks in advance.

    GT
    See here

    lenze
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  10. #10
    GTO
    GTO is offline
    MrExcel MVP
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    5,064

    Default Re: Hide a sheet and Password protect it

    There are examples about of using API to coerce an InputBox into displaying asterisks. IMHO, it is easier to make a simple userform with one textbox and two command buttons (OK and Cancel).

    Create a new userform and plop a textbox onto it. In the properties window, check out the PasswordChar property.

    Hope that helps,

    Mark

Page 1 of 5 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com