Previous Topic Next Topic

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

Answer 1 / 2
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
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
Report

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

Thanks
Answer 2 / 2
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
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
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")
Report

Thanks rizisa
I did get it to work, and as you said it was the "y"

Many thanks
Ask a question