Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Password protecting a sheet

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

    Default

    I have come up with the following code to protect a shhet from being opened unless you know the password. It all works, however the sheet is shown in the background while the password is entered, the question is does anyone know how to stop the sheet being shown in the background. P.S. the code goes on the page code, if anyone wants it feel free to copy it!!

    Private Sub Worksheet_Activate()
    OK = InputBox("Please Enter Your passwor", "Password")
    If OK = 864 Then Sheets("Sheet3").Select
    If OK <> 864 Then Sheets("Sheet1").Select
    End Sub

    the 864 is the password!

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Manchester UK
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    P.S. If anyone knows of a better way of doing this please let me know, also if anyone thinks this is anygood please let me know and thanx to Ivan F Moala and brettvba for the help earlier this is a moddified version of what we tried to do!!

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

    Private pwok As Boolean
    Private Sub Worksheet_Activate()
    If pwok = True Then Exit Sub
    Sheets("Sheet1").Visible = False
    ok = InputBox("Please Enter Your password", "Password")
    If ok = "" Then
    Sheets("Sheet1").Visible = True
    pwok = True
    Sheets("Sheet3").Select
    pwok = False
    Exit Sub
    End If
    If ok <> 864 Then
    pwok = True
    Sheets("Sheet1").Visible = True
    pwok = False
    Sheets("Sheet3").Select
    Exit Sub
    End If
    If ok = 864 Then
    pwok = True
    Sheets("Sheet1").Visible = True
    Sheets("Sheet1").Activate
    pwok = False
    End If
    End Sub


    This hides the sheet (btw I managed to make Sheet1 the hidden one!) until the password is entered. The pwok flag is needed since making the sheet visible calls this routine again! I have also provided for the user hitting cancel.

    Hope this helps

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Manchester UK
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thats excellent Thanks very much. There was a slight problem with the code there was allitle too much of it but that was easily taken care of thanks alot!!

    Private pwok As Boolean
    Private Sub Worksheet_Activate()
    If pwok = True Then Exit Sub
    Sheets("Sheet1").Visible = False
    ok = InputBox("Please Enter Your password", "Password")
    If ok = "" Then
    Sheets("Sheet1").Visible = True
    pwok = True
    Sheets("Sheet3").Select
    pwok = False
    Exit Sub
    End If
    If ok <> 864 Then
    pwok = True
    Sheets("Sheet1").Visible = True
    pwok = False
    Sheets("Sheet3").Select
    Exit Sub
    End If
    If ok = 864 Then
    pwok = True
    Sheets("Sheet1").Visible = True
    Sheets("Sheet1").Activate
    pwok = False
    End If
    End Sub



    I changed it too:

    Private pwok As Boolean
    Private Sub Worksheet_Activate()
    If pwok = True Then Exit Sub
    Sheets("Sheet1").Visible = False
    ok = InputBox("Please Enter Your password", "Password")
    If ok <> 864 Then
    pwok = True
    Sheets("Sheet1").Visible = True
    pwok = False
    Sheets("Sheet3").Select
    Exit Sub
    End If
    If ok = 864 Then
    pwok = True
    Sheets("Sheet1").Visible = True
    Sheets("Sheet1").Activate
    pwok = False
    End If
    End Sub

    i think thats right it seems to work anyways, Tanx again!!

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried using the same code and that works great. Is there anyway to hide the vba code behind the scenes so someone couldn't go in and use the VBA editor to find the password?

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    password protect your vba editor
    right click of your project and goto properties

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
  •