Ask a question Report

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

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

5 replies

Report Respond to rizvisa1

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Nov 15, 2014 at 06:47 AM

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

MartynP 5 Posts Thursday November 13, 2014Registration date March 17, 2015 Last seen - Nov 17, 2014 at 08:19 AM

Thank you

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

Many Thanks

Report

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")

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

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

Your reply