VBA to lock rows based on any input of a specific column

[nur918]

Hi all, I am looking for a VBA code which can lock the rows from column K to P based on any input of the same row's column J.
I would want the row to lock once any info is written in the column J. Do not want it to be specific to a text.
Currently I have:

Private Sub Worksheet_Change(ByVal Target As Range)

Const PASSWORD = "PW"

Dim Row As Long

If Target.Column = 10 Then

Target.Parent.Unprotect PASSWORD

Row = Target.Row

Target.Parent.Range("K:P").Locked = True

Target.Parent.Protect PASSWORD

End If

 End Sub

However, this only locks that particular cell which has value in column J. I would want that row (K to P) to be locked. Cant seem to figure it out. Thanks for the help in advance

 

[Peter_SSs]

Welcome to the MrExcel board!

I would recommend not using words that are used in the vba language as variable names. Since "Password" is one of the argument names in the Protect method I would not use it.
See if this does what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
  Const PWORD = "PW"
  
  With Target.Parent
    If Target.Column = 10 Then
      .Unprotect PWORD
      Intersect(Target.EntireRow, .Range("K:P")).Locked = True
      .Protect PWORD
    End If
  End With
 End Sub

BTW, if a user deletes an entry from column J, the cells in K:P will remain Locked. Is that what you want?

Also, is it possible that a user (or code) enters more than one value in column J at the same time (eg copy/paste or Ctrl+Enter data entry)?

 

[danjuma]

Hi all, I am after something similar to the OP request and tried the VBA code provided by @Peter_SSs above. However, this seems to actually lock the whole sheet as opposed to just the row (i.e. say I wanted row K2:P2 only locked, it is locking the whole sheet - all rows and columns). Also, a code/line of code to unlock the row by someone authorised (me in this case) would be appreciated. Thank you.

​

 

[Peter_SSs]

this seems to actually lock the whole sheet as opposed to just the row (i.e. say I wanted row K2:P2 only locked, it is locking the whole sheet - all rows and columns).

Try this

1. Manually Unprotect the sheet
2. Select the whole sheet by clicking at the intersection of row labels and column labels:
   
3. Format cells - Protection tab - Remove the tick from 'Locked' - OK
   

Now try again.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[danjuma]

Hi Peter_SSs, many thanks for your reply. I actually eventually managed to figure it out before your reply (just as you have described), but many thanks.

 

[Peter_SSs]

You're welcome. Glad you were able to latch on to it yourself.