Alert when a formula gets changed.

I've used this:

Option Explicit

Public bInTable As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

' prevent changes to formula cells
If Cells(1, Target.Column) <> "" Then
    If Not bInTable Then
        Exit Sub
    Else
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
            MsgBox "Please do not make changes in this column", , _
                   "Changes not permitted"
            Exit Sub
        End With
    End If
End If
End Sub

With this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim lLR As Long

' establish range
With Me
    ' last row of data based on column A
    lLR = .Range("A" & .Rows.Count).End(xlUp).Row
End With
    
    If Target.Row <= lLR Then
        bInTable = True
    Else
        bInTable = False
    End If

End Sub

Basically, the Selection Change code determines if the cell selected is in the body of the table and sets a boolean flag. Then, if they make changes in the table area, the Worksheet Change event checks if row 1 is empty. If it's not, that column contains a formula ... the code undoes the change and displays a message.

If they make changes outside the table area, I don't care.

I have also considered checking the colour of the cell. In another project, I have used a Red interior colour to indicate that the column should not be changed and a Green interior colour to say that it can (take manual input) ... although I haven't put the protection code in place on this one.


Regards, TMS

Ok, take out the undo bit and just leave the message.

Think you're wasting your time though. The whole point of having formulae is to calculate stuff properly not to allow people to put whatever crap they want in. Anyway, your workbook, your issue

... instead of the above, creating a whole new workbook from scratch.

If the concept has changed and its full of junk, you'd be better off starting afresh.

Don't waste time, JFDI.


Regards, TMS

Also, there are ways to construct formula that are proof against inserting/deleting columns/rows, but perhaps a fresh start might be better

You're welcome. Thanks for the rep.