Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Durham, North Carolina, USA
    Posts
    140
    Thanks
    5
    Thanked 1 Time in 1 Post

    Hiding sheets with a password?

    Using VBA for Excel, Excel 97, I'm trying to HIDE each sheet in a workbook with a password.

    It looks like Worksheets.hide doesn't support passwords, though worksheet.protect does. Any suggestions? Am I approaching this from the entirely wrong direction?

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hiding sheets with a password?

    Do you mean you're trying to use a password to unhide the sheet, or do you want to both password protect and hide the sheet. If the latter, protect the sheet first. You'll still be able to hide it.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding sheets with a password?

    Check out the Visible Property for a worksheet. Setting this to xlVeryHidden in a VBA statement prevents the user from unhiding it. It can only be unhidden through another VBA statement. All you have to do is figure out a good strategy of where to place those statements.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hiding sheets with a password?

    A couple of comments:

    First, you have to leave at least one sheet visible in the workbook. If you are hiding sheets manually the option to hide the last visible sheet is 'grayed out' (IIRC) but if you are doing it by looping though the "worksheets" collection you will get an error (subscript out of range, I think - it happened to me recently)

    Second, if hide the worksheet(s) then 'protect' the workbook with a password then the user cannot unhide the sheets (the 'unhide' option is grayed out).

    Third, if you are protecting the workbook through VBA and you don't want the user to be able to open the module to find the password (the VBA command to protect the workbook is:
    <font color=blue>ActiveWorkbook.protect password:=Password, Structure:=True, Windows:=False</font color=blue> <- <font color=red>note that it contains the password!</font color=red>)

    you can do what I had to do recently - place the password in a named range on one of the hidden sheets, then access it by:

    Dim Pword as String
    Pword = [pwcell].value
    ActiveSheet.protect password:=Pword

    where pwcell is the named range that contains the password.

    So long as the sheet is hidden the password is hidden (and I used xlveryhidden elsewhere in the routine to make sure it was).

    Finally - if your workbook contains hidden worksheets you have to know the name to access it, you cannot loop through the Worksheets collection and select them one at a time.

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding sheets with a password?

    "Finally - if your workbook contains hidden worksheets you have to know the name to access it, you cannot loop through the Worksheets collection and select them one at a time."

    [img]/w3timages/icons/sad.gif[/img] Oops - unless things have changed in Excel 2000 (and that is what you are using), I'm afraid that in 97 you can still access very hidden sheets via the Worksheets collection. The only thing very hidden does for you is that it stops users unhiding from menus.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hiding sheets with a password?

    "Oops - unless things have changed in Excel 2000 (and that is what you are using), I'm afraid that in 97 you can still access very hidden sheets via the Worksheets collection. The only thing very hidden does for you is that it stops users unhiding from menus."

    Interesting. I am using XL 2000, and when I run:

    Sub HideProtect()
    Dim Sh As Object
    ' Worksheets("hidden").Visible = xlVeryHidden
    For Each Sh In Worksheets
    Sh.Select
    ActiveSheet.Protect password:="Deaner"
    Next Sh
    ActiveWorkbook.Protect password:="Deaner"
    End Sub

    It operates as expected - the sheets and the workbook become protected. When I remove the comment indicator from the 'xlveryhidden' line and run it again with sheets and workbook unprotected I get a "Select method of Worksheet class failed" error message on the "Sh.Select' line

    This is exactly the behaviour when I run the macro as shown (with the xlveryhidden line commented out) but I have previously 'xlveryhidden' a sheet through the immediate window. I had assumed that the worksheets collection was affected by including a 'very hidden' object.

    When I try to run

    Sub UnHide()
    Dim Sh As Object
    For Each Sh In Worksheets
    Sh.Visible = True
    Next Sh
    End Sub

    After I have 'veryhidden' a worksheet and protected the workbook, I also get an error "Unable to set the Visible Property of the Worksheet Class" at the "sh.visible" line, although this macro works when the book is unprotected.

    I think I was confused because I was protecting the w/book, as well as "veryhiding" a sheet within it.

Posting Permissions

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