Lock full row based on a cell value

Mukundan

New Member
Joined
Feb 5, 2025
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
 
Hi @Mukundan.
Enter the following code into the sheet module where you want to protect the range of cells.
VBA Code:
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.
 
Upvote 0
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.
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top