Menu
Ask a question Report

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

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

Report Respond to rizvisa1
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 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
Report
MartynP - Nov 17, 2014 at 06:29 AM
Thanks rizvisa1, did test this and unable to get this to work, didn't seem to lock the cells.

Thanks
Report Respond to MartynP
Posts
5
Registration date
Thursday November 13, 2014
Last seen
March 17, 2015
 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
Report
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 - 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")
Report
Martynp - Nov 19, 2014 at 08:38 AM
Thanks rizisa
I did get it to work, and as you said it was the "y"

Many thanks
Ask a question