Lock cell in Excel worksheet with VB [Solved/Closed]

Report
Posts
5
Registration date
Thursday November 13, 2014
Status
Member
Last seen
March 17, 2015
-
 Martynp -
I have an worksheet that I need to lock the cell based on the value of another cell. I have managed to create VB which will do this for one row but need this to work for up to 1000 rows.

I use columns A to Q and rows 7 to 1000, when "Y" is entered in Q7 the cells A7 to Q7 lock

the VB I have created is below (which will lock row 7)


Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect

If ActiveSheet.Cells(7, 13).Text = "Y" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range(Cells(7, 1), Cells(7, 13)).Locked = True
Else
ActiveSheet.Range(Cells(7, 1), Cells(7, 13)).Locked = False
End If
If ActiveSheet.Cells(8, 13).Text = "Y" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range(Cells(8, 1), Cells(8, 13)).Locked = True
Else
ActiveSheet.Range(Cells(8, 1), Cells(8, 13)).Locked = False
End If
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

I know I will not be able to add each row as it will be to large, so is there a more efficient way to do this.

Thanks

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
I have not really tested this one. But i think it should work for u


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

If (Intersect(Target, Range(Cells(1, "H"), Cells(1000, "H"))) Is Nothing) Then
Exit Sub
End If

ActiveSheet.Unprotect ("")
For Each Row In Target.Rows
rowId = Row.Row
Range(Cells(rowId, "A"), Cells(rowId, "H")).Locked = (Cells(rowId, "H") = "Y")
Next

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub
Thanks rizvisa1, did test this and unable to get this to work, didn't seem to lock the cells.

Thanks
Posts
5
Registration date
Thursday November 13, 2014
Status
Member
Last seen
March 17, 2015

Hi rizvisa1
I think I have sorted this, just going to do some testing.

Many Thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Works for me. Only thing that i can think of is that you may have "y" instead of "Y". in that case you can change the line

(Cells(rowId, "H") = "Y")
to

(UCase(Cells(rowId, "H")) = "Y")
Thanks rizisa
I did get it to work, and as you said it was the "y"

Many thanks