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

excelpunk

Board Regular
Joined
May 6, 2011
Messages
165
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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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:

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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top