I want to protect a cell but allow formula values only

  • Thread starter Thread starter Fredy A.
  • Start date Start date
F

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.
 
Values calculated by formulas will continue to be updated despite sheet
protection.
 
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.
 
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.
 
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
 
Back
Top