Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Protecting a Hidden Sheet

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Ireland
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there any way to hide a sheet and request a password before the sheet can be un-hidden? I need to do this to Multiple sheets (namely 2001, 2002, 2003) but I don't want the password to be asked 3 times, just once to unhide all the sheets together. I know that this will probably have to be done using VBA and this is not a problem. Please can somebody help???????

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you hide allt he sheets you want to and then protect the workbook with a password, the following will unprotect and unhide all sheets (including charts) you have hidden: -

    Public Sub UnprotectandUnhide()

    Dim sh As Object
    Dim Pass As String

    Pass = InputBox("Please enter password to unhide sheets")

    On Error GoTo ErrorHandler
    ActiveWorkbook.Unprotect Pass

    For Each sh In Sheets
    If sh.Visible = False Then sh.Visible = True
    Next sh

    Exit Sub

    ErrorHandler:
    MsgBox "Wrong password, please re-enter"
    On Error GoTo 0

    End Sub

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Ireland
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tht's exactly what I need. You're a genius!!!!! Thanks. I've got an Auto_Close Macro which hides the sheets if they're visible. The Macro you gave me is just perfect. However I have 2 questions. When I record the Auto_Close Macro, is there any way to specify the password? I don't want the user to be prompted for it. Secondly, not as important. When I type the password at the prompt (the one from your macro) the password appears. It's not protected from others viewing it. Can we change it so that it's protected and just appears as *'s???? I'm probably explaining myself very badly. Thanks again.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    For the Auto_Close macro, you could just use: -

    ActiveWorkbook.Protect Password := "YourPassword"

    As for the asterisks, I believe you can do this with a UserForm, but not with a normal InputBox. Maybe Juan Pablo or one of the other experts could help you out with that.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just realised, you could use a UserForm with a command button and a text box. Right-click on the textbox and select properties. Change the PasswordChr from blank to an asterisk. Double-click on the command button and enter the following in it's click-event: -

    Call UnprotectandUnhide

    Then amend the sub above to the one below: -

    Public Sub UnprotectandUnhide()

    Dim sh As Object

    On Error GoTo ErrorHandler
    ActiveWorkbook.Unprotect UserForm1.TextBox1.Text

    For Each sh In Sheets
    If sh.Visible = False Then sh.Visible = True
    Next sh

    Unload UserForm1
    Exit Sub

    ErrorHandler:
    MsgBox "Wrong password, please re-enter"
    On Error GoTo 0
    Unload UserForm1

    End Sub

    You'll have to work out some way of showing the UserForm, maybe a command button on the sheet would be best.

    HTH.

    [ This Message was edited by: Mudface on 2002-03-01 05:20 ]

Some videos you may like

User Tag List

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
  •