I want to protect a cell but allow formula values only

[Fredy A.]

Hello, i have a worksheet with some formulas in it. I
have the sheet protected, but there are some cells i
cannot protect because if i do, values calculated by
formulas will not populate them. When i leave them
unprotected, there is a risk of people typing their own
value in and erasing the formula. How can i truly protect
my sheet? I want to alllow data to be populated in
certain cells, but only by formulas, not by a user typing
in whatever they want. Your help greatly appreciated.

 

[Vasant Nanavati]

Values calculated by formulas will continue to be updated despite sheet
protection.

 

[Frank Kabel]

Hi
you can simply protect the cells with formulas. they will
get calculated if the cells referenced by this formula are
changed. So no problem protecting your formula cell.

 

[Guest]

Put this in your worksheets SelectionChange event. this will automatically force the selection down by one row anytime it encounters a cell with a formula. You can change it to 'Target.Offset(0, 1).Select' to force a column change depending on how your woksheet flow is directed. also you may have save and close the workbook the first time to activte i

Private Sub Worksheet_SelectionChange(ByVal Target As Range
If Target.Cells.Count = 1 The
If Target.HasFormula Then Target.Offset(1, 0).Selec
End I
End Su


----- Fredy A. wrote: ----

Hello, i have a worksheet with some formulas in it. I
have the sheet protected, but there are some cells i
cannot protect because if i do, values calculated by
formulas will not populate them. When i leave them
unprotected, there is a risk of people typing their own
value in and erasing the formula. How can i truly protect
my sheet? I want to alllow data to be populated in
certain cells, but only by formulas, not by a user typing
in whatever they want. Your help greatly appreciated.

 

[Tim Zych]

In the sheet's change event undo any non-formula entry.
Might be difficult to implement in the real world for a variety of reasons
but here's a start.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target(1, 1).HasFormula Then
Application.Undo
End If
End Sub