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.