Lock cell/row/range when a specific cell is not empty

SahanTW 

This takes several steps.

 

1) Select all cells that the user should be able to edit initially.

Press Ctrl+1 to activate the Format Cells dialog.

Activate the Protection tab.

Clear the Locked check box.

Click OK.

 

2) Activate the Review tab of the ribbon.

Click 'Protect Sheet'.

Use the check boxes to specify what the user can do.

At the least, leave the check box 'Select unlocked cells' ticked.

If you want to provide a password that will be needed to unprotect the sheet, enter it in the box. Do not forget it!

Click OK. If you entered a password, you will have to enter it again as confirmation.

 

3) Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("C2:C1000"), Target) Is Nothing Then
        Me.Unprotect Password:="Secret"
        For Each rng In Intersect(Range("C2:C1000"), Target)
            rng.EntireRow.Locked = (rng.Value <> "")
        Next rng
        Me.Protect Password:="Secret"
    End If
End Sub

Change C2:C1000 to the range that should cause a row to be locked if a value is entered.

Change "Secret" to the password you specified when protecting the sheet (use "" if you didn't).

 

4) Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open it.