Password protect cell

[VbaHell]

Hello all

Is it possible please to password protect a cell from being changed without a password

I would like pop up box when the cell value changes to "Yes" prompting for a password first.

This would be for any cell change in column "P"

I was trying to use something like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pword As String
  If Not Intersect(Columns("P"), Target) Is Nothing Then
    If [(P:P,"="Yes"")] Then
    
    pword = Application.InputBox("Enter password", "Password", Type:=2)
If pword <> "test" Then Exit Sub
      Target.Select
    End If
  End If
End Sub

 

[Scott T]

Try

Note your current code just pops up the input box but if the password is not correct it just exits the sub so it does not prevent anything.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pword As String

  If Target.Column = 16 And UCase(Target) = "YES" Then
    pword = Application.InputBox("Enter password", "Password", Type:=2)
    If pword <> "test" Then Exit Sub [COLOR=#00ff00]'this does not prevent the changing of the cell it just exits the sub[/COLOR]
      Target.Select
    End If
End Sub

 

[VbaHell]

Hi Scott

I see now what you mean, I have tried the code you provided and it asks for the password but it does not stop the change even if you fail to provide the password

Is it possible to enter the value "No" in the cell if the correct password is not entered

 

[tyija1995]

Is this something you are looking for? Slightly edited the code previously posted:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pword As String
  If Not Intersect(Columns("P"), Target) Is Nothing Then
    If UCase(Target.Value) = "YES" Then
        pword = Application.InputBox("Enter password", "Password", Type:=2)
            If pword <> "test" Then
                Target.Value = "No"
                Range("O1").Select
            End If
    End If
  End If
End Sub

 

[Scott T]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pword As String
  If Target.Column = 16 And UCase(Target) = "YES" Then
    pword = Application.InputBox("Enter password", "Password", Type:=2)
    If pword <> "test" Then Target = "No"
      Target.Select
    End If
End Sub

Note that VBA is case sensitive so your password is also case sensitive. If someone entered TEST it would not be a match since you have it in lowercase. If you do not want the password to be case sensitive you can change the line to

If UCase(pword) <> "TEST" Then Target = "No"

This will make what was entered all caps and test against all caps string that is your password.

 

[VbaHell]

Thank you both for your replies. just what I want, I also put a data validation on this field to stop caps being enter
Thanks again