Solved: password lock a worksheet using a userform

[Pete]

see attached workbook
Hi

Need to password proect one worksheet in the workbook. so if the user click the tab "Pricing Template" then they are prompt to enter a password.

Sequence

1. If they press ok button with out entering password msg box" Invaild or Incorrect Password Entered" - Try Again!

2. If they press cancel then exit

3. Correct password open up worksheet.

4. If they switch worksheet(s) then the password must be enter again to access worksheet once again..

The password for the workbook is Phoenix if asked - User frm is frm_Pricing_Template.......

[Kenneth Hobs]

I did not see a userform called frm_Pricing_Template. I guessed that you meant frm_Password_Pricing.

In this method, I stored the state to activate in the registry.

[VBA]
'In the userform frm_Password_Pricing:
Private Sub cmdCancel_Click()
Sheet1.Activate
Unload Me
End Sub

Private Sub cmdOK_Click()
If txtPassword.value = "Ken" Then
Unload Me
SaveSetting ThisWorkbook.Name, "Pricing Template", "Activate", "True"
Worksheets("Pricing Template").Activate
Exit Sub
Else
MsgBox "Incorrect Password", , "Again"
txtPassword.SetFocus
Exit Sub
End If
End Sub

'In ThisWorkbook:
Private Sub Workbook_Open()
SaveSetting ThisWorkbook.Name, "Pricing Template", "Activate", "False"
Sheet1.Activate
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Pricing Template" Then
If GetSetting(ThisWorkbook.Name, "Pricing Template", "Activate") = "False" Then
Sheet1.Activate
frm_Password_Pricing.Show
End If
End If
End Sub[/VBA]