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

MartynP 5 Posts Thursday November 13, 2014Registration date March 17, 2015 Last seen - Nov 13, 2014 at 09:34 AM - Latest reply:  Martynp
- Nov 19, 2014 at 08:38 AM
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
See more 

5 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Nov 15, 2014 at 06:47 AM
0
Thank you
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
MartynP 5 Posts Thursday November 13, 2014Registration date March 17, 2015 Last seen - Nov 17, 2014 at 08:19 AM
0
Thank you
Hi rizvisa1
I think I have sorted this, just going to do some testing.

Many Thanks
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Nov 19, 2014 at 06:54 AM
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