Need to protect a formula in a way that allows the use of a downward fill

[mwhassan]

Hello all,

I need some help. I have a formula that I need to protect but can be copied and pasted or filled in a downward direction. i don't care if the subsequent formulas are protected or not. When I use lock and protect I can not fill downward, or copy and paste. I used the data validation tool which works perfect except the formula can still be deleted, which I would like to prevent, trying to keep my sheet as idiot proof as possible.

Thanks!!

 

[baitmaster]

couple of suggestions:

1) create a named formula if you can. This way you preserve the formula within the name, and only have to refer to the name
2) learn to write the formula using VBA, so it can be reset automatically
3) use VBA "worksheet_change" event, to identify if the formula is accidentally deleted, and undo that action. This example should undo any changes to cell A1:

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Column = 1 And Target.Row = 1 Then
    MsgBox "you can't change that section", vbCritical
    Application.Undo
End If
 
end sub

add it into the worksheet code module

 

[mwhassan]

Sorry to ask one more question, how would I the use that for a range? I have formulas in cells A9:AN9.

Thanks!!

 

[baitmaster]

It can become more complex when dealing with a range of cells, as you may need to consider other changes to the same sheet. For your current requirement I would try

Private Sub Worksheet_Change(ByVal Target As Range)
 dim cl as range

for each cl in target
    If cl.Column >= 1 And cl.Column <= 14 And Target.Row = 9 Then
        MsgBox "you can't change that section", vbCritical
        Application.Undo
        exit for
    End If
 next cl

end sub

This looks at each cell that is being changed, asks if it is in the locked region, and if so forces Undo then ends the macro

The reason for looking at each cell you are changing, and comparing each to the range that is locked, is that if the User selects a range of cells that overlaps your locked region, VBA will only look at your activecell (usually top left) - which might not be in the locked region, even though other cells are

REMEMBER once a macro is run, you lose the Undo feature, you cannot recover any data lost... Always back-up before using and understanding untested code!

 

[mwhassan]

That's perfect thank you so much for your help.