password protect a checkbox with userform

[AlexPi]

I am trying to set up a spreadsheet to lock specific cells if a check box is clicked. I have gotten the locked cells to work but am trying to take it one step further and put a password on the checkbox so that only a manger (with the password) can lock or unlock the cells and then be able to add or delete data in the specific cells.

I have created a userform which prompts for a password when the checkbox is clicked. The only trouble I am having is linking the userform and the checkbox....

When I click the checkbox the userform appears and asks for a password. If the correct password is entered the userform closes, if incorrect another msg box appears stating it was an invalid password. Does anyone have any ideas as to get this to work? Right now, a user could click the checkbox then when prompted, click cancel, and the check box will change (checked or unchecked). I would like it so that the checkbox would be change being checked or unchecked only if the correct password is given.

Any ideas would be greatly appreciated.

 

[wigi]

For instance:

(the password used is "pa$$w0rd")

If MsgBox("Please provide the password") = "pa$$w0rd" Then
   'checkbox set to True
Else
   'checkbox set to False
End If

That MsgBox has other arguments, for example which buttons to show or the banner at the top of the window.

Wigi

 

[pedie]

Option Explicit
Private Sub CheckBox1_Click()
 If ActiveSheet.CheckBox1 Then
  UserForm1.Show
  Else
  Sheet1.Protect Password:="123"
 End If
End Sub
 
 
Private Sub CommandButton1_Click()
If Me.TextBox1.Value <> "pwd123" Then
MsgBox "Incorrect password entered! please try again...", vbCritical, "Invalid Password"
Exit Sub
Else
Unload Me
Sheet1.Unprotect Password:="123" 'sheetpassword
End If
End Sub

 

[AlexPi]

Thank you both for the quick reply! I think I'm missing something here...

Pedie, This is the code I was using to lock the cells. How can I incorporate this with your code?

Private Sub approval_Click()
userform1.Show
Dim myRng As Range
Dim myPwd As String
myPwd = "secret"
Set myRng = Me.Range("D3:D60")
Me.Unprotect Password:=myPwd
myRng.Locked = Me.Approval.Value
Me.Protect Password:=myPwd
End Sub

 

[pedie]

I think when we protect it protects the whole sheet...unless you want all range not be protected

i think we can do it this way too...

Select All cells [ctrl + A] then
ctrl + 1
protection tab > uncheck locked
hit ok.
select the range you want to protect
ctrl + 1
protection tab > check locked
hit ok.

[/FONT]
[FONT=Courier New]'Place this code in sheet module[/FONT]
[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New]Private Sub CheckBox1_Click()[/FONT]
[FONT=Courier New] If ActiveSheet.approval Then[/FONT]
[FONT=Courier New]  UserForm1.Show[/FONT]
[FONT=Courier New] Else
  Sheet1.Protect Password:="123"
[/FONT]
[FONT=Courier New] End If[/FONT]
[FONT=Courier New]End Sub[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]'This code in userform CommandButton1[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]Private Sub CommandButton1_Click()[/FONT]
[FONT=Courier New]If Me.TextBox1.Value <> "pwd123" Then 'this is where we enter the password[/FONT]
[FONT=Courier New]MsgBox "Incorrect password entered! please try again...", vbCritical, "Invalid Password"[/FONT]
[FONT=Courier New]Exit Sub[/FONT]
[FONT=Courier New]Else[/FONT]
[FONT=Courier New]Unload Me[/FONT]
[FONT=Courier New]Sheet1.Unprotect Password:="123" 'sheetpassword[/FONT]
[FONT=Courier New]End If[/FONT]
[FONT=Courier New]End Sub[/FONT]
[FONT=Courier New]