Password protecting a sheet

[jamienwood]

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!

 

[jamienwood]

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!!

 

[davidtrickett]

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

 

[jamienwood]

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!!

 

[wcassell]

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?

 

[brettvba]

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