Lock cell in Excel worksheet with VBSolved/Closed

Question

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

rizvisa1 · Answer

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:=TrueEnd Sub

MartynP · Answer

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

Lock cell in Excel worksheet with VB

2 replies

Subscribe To Our Newsletter!