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

Thread: How to restrict user to stop editting a formula in a cell but can enter any value if required.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    163
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    Board Regular skorpionkz's Avatar
    Join Date
    Oct 2013
    Location
    Dublin
    Posts
    1,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Regards,
    Andrzej (Andrew) Bejmart

    ------------------------------------------------------------------------
    Any fool can know. The point is to understand. - Albert Einstein

    Windows 10, Office 2013, Home PC
    Windows Server Datacenter, Office 2010, Work - Citrix Server

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •