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

excelpunk

Board Regular
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

Well-known Member
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

Well-known Member
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
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top