CODEWITHSUNDEEP
vbaexcel
James
James

Reputation: 499

Locking specific cells in excel VBA

I have a macro I am working on currently.

Purpose The objective in that once there is a value in column L , i need cells in the revelant row to lock. However this should not lock the whole worksheet, just the cells.

Code Below is the code i have been been playing with. I am trying amend the code so to achieve my purpose above.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row = 1 Then
        If Target.Column = 3 Or Target.Column = 5 Then
            Beep
            Cells(Target.Row, Target.Column).Offset(0, 1).Select
        End If
    End If
End Sub

Example enter image description here

The cells which will be locked are in yellow, if there is a date in column L

The reason why locking the whole worksheet wont overcome the problem is because any additional users will not be able to input there own data into the worksheet.

Upvotes: 0

Views: 10607

Answers (2)

user4066647
user4066647

Reputation:

Merely locking the cells has no effect unless you protect the sheet. Firstly, unlock all the cells in the sheet, and then try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 12 Or Target.Row = 1 Then Exit Sub

    Range(Cells(Target.Row, 2), Cells(Target.Row, 11)).Locked = True
    ActiveSheet.Protect
End Sub

Upvotes: 2

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9966

Change the password in the first line of code with the actual password to unprotect the sheet.

Const PW As String = "123"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim r As Long
If Target.Column = 12 And Target.Row > 1 Then
    ActiveSheet.Unprotect Password:=PW
    r = Target.Row
    If Target <> "" Then
        Range("B" & r & ":K" & r).Locked = True
    Else
        Range("B" & r & ":K" & r).Locked = False
    End If
    ActiveSheet.Protect Password:=PW
End If
End Sub

Upvotes: 0

Related Questions