How to restrict user to stop editting a formula in a cell but can enter any value if required.

[excelpunk]

Hello,

I am not sure if this is possible but I have a situation, where, few cells have formulas in them. I want the cell to be editable by the user where they can overwrite the formula with other numbers but at the same time I dont want them to give the ability to edit the formula inside the cell. Is this possible?

 

[Gerald Higgins]

Hi, I don't think this is possible.
Either you give people access to change the cell contents (by inputting a new value or editing the formula), or you don't give them access.

Consider perhaps using 2 different cells, one to contain the formula, and one to contain the manually input value if entered.
Then the formula could say something like
=IF( ISBLANK( [Manually Input Value] ) , [ your existing formula goes here ] , "" )

And if other formulas depend on this formula, make them refer to either this formula, OR the manually input value if it exists.

 

[skorpionkz]

Hi excelpunk,

there is not default way for something like that. You can achieve it with simple code, but I would advice to consider if you really want user to overright formulas with values.

Any way here is how you can do something like that. In the worksheet object paste below code:

Private v_Value     As Variant
Private str_Formula As String


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newValue    As Variant
    Dim newFormula  As String
    
    Application.EnableEvents = False
    newValue = Target.Value
    newFormula = Target.Formula
    
    If newValue = newFormula Then Exit Sub
    If Not str_Formula = Empty Then Target.Formula = str_Formula
    Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    v_Value = Target.Value
    str_Formula = Target.Formula
    
    If str_Formula = v_Value Then str_Formula = Empty
End Sub