Lock and protect selected excel rows with VBA code

Check it out now

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lngCtr As Long

Const cstrPW As String = "password"

If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B2:D11")) Is Nothing Then

  For lngCtr = 2 To 11
    If WorksheetFunction.CountBlank(Range("B" & lngCtr & ":D" & lngCtr)) = 0 Then
      ActiveSheet.Unprotect cstrPW
      Range("B" & lngCtr & ":D" & lngCtr).Locked = True
    Else
      Range("B" & lngCtr & ":D" & lngCtr).Locked = False
    End If
  Next lngCtr
  ActiveSheet.Protect cstrPW
End If

End Sub

@NeedForExcel:
Please enter Test into Cell B11 alone and run your code in your attachment. I doubt that all empty rows between 3 and 10 should be locked as well.

Ciao,
Holger

Originally Posted by HaHoBe

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lngCtr As Long

Const cstrPW As String = "password"

If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B2:D11")) Is Nothing Then

  For lngCtr = 2 To 11
    If WorksheetFunction.CountBlank(Range("B" & lngCtr & ":D" & lngCtr)) = 0 Then
      ActiveSheet.Unprotect cstrPW
      Range("B" & lngCtr & ":D" & lngCtr).Locked = True
    Else
      Range("B" & lngCtr & ":D" & lngCtr).Locked = False
    End If
  Next lngCtr
  ActiveSheet.Protect cstrPW
End If

End Sub

@NeedForExcel:
Please enter Test into Cell B11 alone and run your code in your attachment. I doubt that all empty rows between 3 and 10 should be locked as well.

Ciao,
Holger

Hi Holger,

I have kept that in mind, however keeping in mind, normally data entry will happen one row after another, that's why like that..

Hi, NeedForExcel,

no need for a full quote here - maybe have a look into the Forum Rules (#12).

Any entry into Column B would have to leave Column C and D open for more entries in that row or how will you fill the entries in these columns when the row is locked?

Ciao,
Holger

Originally Posted by HaHoBe

Any entry into Column B would have to leave Column C and D open for more entries in that row or how will you fill the entries in these columns when the row is locked?

Agreed! But the user will lock the Row only when he finishes filling all the 4 Columns on that particular row wont he? Atlease that's what I think.

Best if the OP states if he requires something otherwise.

Hi, NeedForExcel,

the yellow highlighted area holds Columns B to D (which is 3 Columns) and the rows 2 to 11. With your code (which could be started at any time even if not entry or change was made) the whole area between B2 and F and the last entry row in Column B is blocked. I can´t agree on that from what I read that from post #10.

Ciao,
Holger