Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Feb 2005
    Location
    Florida, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passwording a sheet, not workbook (2000)

    Hi all,

    I would like to have a user enter a password to view a sheet's content.
    I don't care if the sheet is protected or not. I just want him to type in a password and view the content.

    Coudn't do it. I'll appreciate any help.

    -Tivoli0

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passwording a sheet, not workbook (2000)

    It can be done, but it'll probably be fragile.

    Make the sheet very hidden (in the Visual Basic Editor).
    Place a command button on another, visible sheet that prompts the user for a password and unhides the other sheet if the password is correct.
    Create a Workbook_BeforeSave event procedure in the ThisWorkbook module that makes the sheet very hidden again.
    Protect the Visual Basic code in the workbook with a password.

    If the user doesn't enable macros, there is no way to unhide the sheet.

  3. #3
    Lounger
    Join Date
    Feb 2005
    Location
    Florida, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passwording a sheet, not workbook (2000)

    Hi Hans,

    Thanks for your reply.
    I didn't know Excel hasn't got a "built-in" mechanism tp password a sheet.
    I've applied your suggestion and it works fine, "Fragile" is OK.

    Thanks again! -Tivoli0

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passwording a sheet, not workbook (2000)

    Just out of curiosity, why do you suggest hiding the sheet when the user saves? What about the Workbook_BeforeClose event? I'm just thinking it may be frustrating to keep unhiding the sheet each time the workbook saves.
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passwording a sheet, not workbook (2000)

    I suggested that to ensure that the saved version of the workbook always has the worksheet hidden. I assume that the workbook is stored in a shared network folder. If the user can save the workbook with the sheet visible, anyone else can open the workbook in a second instance of Excel and see the worksheet without providing a password.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passwording a sheet, not workbook (2000)

    I see your point if it is on a network. Thanks for clarifying that.
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passwording a sheet, not workbook (2000)

    Even if the workbook is not on a shared network drive, you'd have a problem if Excel crashed after saving the workbook with the sheet visible, but before closing it. Next time the workbook is opened, the sheet would be visible.

Posting Permissions

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