Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Me.Range("A1").Value = "INVOICED" Then
Me.Range("A1:I1").Locked = True
Else
Me.Range("A1:I1").Locked = False
End If
Me.Protect password:="12345", UserInterfaceOnly:=True
If Not Intersect(Target, Me.Range("A1:I1")) Is Nothing Then
Dim password As String
password = "12345" ' Set your password
Dim ePass As String
ePass = InputBox("Enter password to edit cells: ")
' If the password is correct, remove the protection
If ePass = password Then
Me.Unprotect password:=password
Else
' If the password is incorrect, we display a message
MsgBox "Incorrect password! Changes will not be saved! ", vbExclamation
' Rolling back changes
Application.Undo
End If
End If
End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim password As String
password = "12345" ' Replace with your password
' Check if the value "INVOICED" is contained in the range A1:I1
If Not Intersect(Target, Me.Range("A1:I1")) Is Nothing Then
For Each cell In Me.Range("A1:I1")
If cell.Value = "INVOICED" Then
' Block range A1:I1
Me.Range("A1:I1").Locked = True
' Protecting a sheet with a password
Me.Protect password:=password, UserInterfaceOnly:=True
MsgBox "Range A1:I1 is blocked! ", vbInformation
Exit For
End If
Next cell
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1:I1")) Is Nothing Then
If Me.ProtectContents Then
Dim password As String
password = "12345" ' Replace with your password
Dim ePass As String
ePass = InputBox("Enter password to edit cells:")
' If the password is correct, remove the protection
If ePass = password Then
ThisWorkbook.Worksheets("Sheet1").Unprotect password:=password
Else
' If the password is incorrect, we display a message
MsgBox "Incorrect password. Changes will not be saved! ", vbExclamation
End If
Else
MsgBox "The sheet is not protected!"
End If
End If
End Sub