Lock certain cells in the same row based on updates by a formula

[intensified89]

I'm trying to lock certain cells within the same row whenever one of the cell is being updated by a formula. This will be repeated for multiple rows. Currently I have the following macro, but it only allows the update whenever the user manually inputs, but doesn't work for updates by formula.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    ActiveSheet.Unprotect
   
    If Not Intersect(Target, Range("AM:AM")) Is Nothing Then Exit Sub

    Select Case Target.Value
        Case "L"
            Range("T" & Target.Row & ":AA" & Target.Row).Locked = True
            Range("AG" & Target.Row & ":AH" & Target.Row).Locked = True
        Case Else
            Range("T" & Target.Row & ":AA" & Target.Row).Locked = False
            Range("AG" & Target.Row & ":AH" & Target.Row).Locked = False
    End Select
    
    ActiveSheet.Protect UserInterfaceOnly:=True, AllowFiltering:=True
    
End Sub

 

[gordsky]

As far as i remember Worksheet_Change does not trigger when the change is via a formula so you would need to use worksheet_calculate to have it trigger this way. the downside to that is that I dont think Worksheet_calculate can use the target property and will trigger for any calculate event on the sheet.

 

[intensified89]

Would it be possible to check for 1 particular cell and once it changes based on the formula, trigger the update?

 

[gordsky]

Would it be possible to check for 1 particular cell and once it changes based on the formula, trigger the update?

As above, worksheet_Calculate will trigger on every formula change and it doesnt recognise target property so will occur regardless of cell.

So really you have two options.

1) Modify your code as below which will loop through column "AM" looking for "L" on every formula change. You may or may not notice it depending on the number of rows you have in a sheet.

2) Assuming there must be some sort of manual entry by a user in another cell that causes the formula in "AM" to update you could focus your worksheet change event (target) onto that cell rather than "AM"

Private Sub Worksheet_Calculate()

Dim cel As Range, MyRng As Range
Set MyRng = ActiveSheet.Range("AM:AM")

ActiveSheet.Unprotect
 
    For Each cel In MyRng

       If cel.Value = "L" Then
            Range("T" & cel.row & ":AA" & cel.row).Locked = True
            Range("AG" & cel.row & ":AH" & cel.row).Locked = True
       Else
            Range("T" & cel.row & ":AA" & cel.row).Locked = False
            Range("AG" & cel.row & ":AH" & cel.row).Locked = False
       End If
  
    Next cel
  
ActiveSheet.Protect UserInterfaceOnly:=True, AllowFiltering:=True
 
End Sub

It may also be worth adding a lastrow reference and checking if the cells are already locked to speed things up

 

[intensified89]

I tried the mentioned code, but faced the runtime error '1004': Unable to set the Locked property of the Range class for the following line.

            Range("T" & cel.Row & ":AA" & cel.Row).Locked = False

 

[gordsky]

are there any merged cells within that range or is the sheet still protected

 

[intensified89]

There are no merged cells from Row 5 onwards (the macro is required for Row 5 onwards). The particular sheet is also unprotected.

 

[gordsky]

are there any merged cells within that range

As you didnt mention there were merged cells or it needed to start at row 5 that is why it will fail. It will start in row1 hit a merged cell and then fail.
delete this line

Set MyRng = ActiveSheet.Range("AM:AM")

and replace it with this

Dim lrow as long
lrow = activesheet.cells(rows.count,"AM").end(xlup).row
Set MyRng = activesheet.range("AM5:AM" & lrow)

 

[intensified89]

I forgot to mention that there are manual entry via a drop down box at J5 to M5, which produces the result in column AM via a formula.

 

[gordsky]

see post#8