Lock full row based on a cell value

[Mukundan]

Hi,

Please assist to lock my excel. I would like to lock my row A1 to I1 if the cell value comes "INVOICED". Additionally, required to edit with password access. Somebody assist to VBA code this

 

[MikeVol]

Hi @Mukundan.
Enter the following code into the sheet module where you want to protect the range of cells.

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

Read the comments in the code because you need to specify/change your password yourself. Mine is just 12345. I hope I understood you correctly and my solution helped you solve your question. Good luck.

 

[MikeVol]

Please consider my previous post with the code as wrong. I misunderstood the question. Here is the updated code, I hope now I understand the question asked. Enter the following code into the sheet module where you want to protect the range of cells.

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

Good luck.

 

[Mukundan]

Dear,

Thanks for your help,

How ever there some thing I missed, Actually i would like to lock each cell if the Cell value comes "INVOICED" And it should be editable with password. my excel screenshot attached for better understanding. If I cell value comes : INVOICED" i need to lock that particular row only and it editable with password lock. if I Cell value comes "Pending" nothing to do and always open for edit without any password. Kindly assist

 

[MikeVol]

Hello @Mukundan,
Ok, Delete all previous code in the worksheet module and replace with the following code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
   
    Dim PWORD       As String
    PWORD = "12345"            ' Replace with your password

    Application.EnableEvents = False

    With Target.Parent

        If Target.Column = 9 And Target.Value = "INVOICED" Then
            Intersect(Target.EntireRow, .Range("A:I")).Locked = True
        Else

            If Intersect(Target.EntireRow, .Range("A:I")).Locked = True Then

                Dim ePass As String
                ePass = InputBox("Enter password to remove protection:")

                If ePass = PWORD Then
                    Intersect(Target.EntireRow, .Range("A:I")).Locked = False
                    MsgBox "Protection removed!", vbInformation
                Else
                    MsgBox "Incorrect password. Unable to remove protection!", vbExclamation
                   
                    ' Undo last change
                    Application.Undo
                End If

            End If

        End If

    End With

    Application.EnableEvents = True
End Sub

Read the comments in the code because you need to specify/change your password yourself. Mine is just 12345. I hope I understood you correctly and my solution helped you solve your question. Good luck.