Lock cells in Excel

Solved/Closed
Report
Posts
5
Registration date
Thursday November 13, 2014
Status
Member
Last seen
March 17, 2015
-
 MartynP -
After setting up VB to lock cells in Excel I now need to lock additional cells. I currently have the following script (kindly given by rizvisa1) which is working well.
Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Application.Intersect(Target, Range("M7:M1005")) _
Is Nothing) Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
If Not (Application.Intersect(Target, Range("R7:R1005")) _
Is Nothing) Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If

Dim rowId As Long
If (Intersect(Target, Range(Cells(1, "M"), Cells(1005, "M"))) Is Nothing) Then
Exit Sub
End If
ActiveSheet.Unprotect ("")
For Each Row In Target.Rows
rowId = Row.Row
Range(Cells(rowId, "A"), Cells(rowId, "M")).Locked = (Cells(rowId, "M") = "Y")
Next
ActiveSheet.Protect ("")

End Sub


First part just makes sure columns "M" and "R" are in uppercase. the second part will lock cells in columns "A" to "M" for the row that has a Y in column "M", this is working fine.


What we have done now is add additional columns "N" to "R" and now want to incorporate the ability to lock cells "N" to "R" for the row that has a Y in column "R".


Tried this several ways but cannot get it to lock the cells.
Note: not all rows will have a Y in column "R"

Any Help would be appreciated

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
It does not work because exit sub

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim rowId As Long

'check in the area that was change contains at least one cell from M1:M1005 or R1:R1005
If (Intersect(Target, Union(Range(Cells(1, "M"), Cells(1005, "M")), Range(Cells(1, "R"), Cells(1005, "R")))) Is Nothing) Then
' no mactch, so nothing to do
Exit Sub
End If

'have some thing to so first unprotect sheet
ActiveSheet.Unprotect ("")

'for each row that have been modified, check
For Each Row In Target.Rows
rowId = Row.Row

'check lock/unlock cells A:M
Range(Cells(rowId, "A"), Cells(rowId, "M")).Locked = (UCase(Cells(rowId, "M")) = "Y")

'check lock/unlock cells N:R
Range(Cells(rowId, "N"), Cells(rowId, "R")).Locked = (UCase(Cells(rowId, "R")) = "Y")
Next
ActiveSheet.Protect ("")

End Sub
Many thanks again rizvisa1, hopefully that is all they now need.