Protected Formulas

This UDF routine should do the job.

Put this in a normal code module.

Option Explicit

Public Const functionName As String = "cellentry()"

Function CellEntry(Optional ByVal inputCell As Range) As String
Rem returns the text last entered in the cell
Rem validation.InputMessage holds CellEntry value:  .ErrorMessage holds formula
On Error Resume Next
If inputCell Is Nothing Then Set inputCell = Application.Caller
On Error GoTo 0
If inputCell Is Nothing Then
    CellEntry = vbNullString
Else
    With inputCell.Range("a1").Validation
        On Error Resume Next
        CellEntry = .InputMessage
        If .Parent.Address <> Application.Caller.Address Then Exit Function
        On Error GoTo 0
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertInformation, Formula1:="=(1=1)"
        .ErrorMessage = .Parent.FormulaR1C1
        .InputMessage = CellEntry
        .ShowInput = False
        .ShowError = False
    End With
End If
End Function

And put this in the ThisWorkbook code module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim myRange As Range, oneCell As Range, xVal As Variant
On Error Resume Next
Set myRange = Target.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If myRange Is Nothing Then Exit Sub
For Each oneCell In myRange
    With oneCell
        If .HasFormula Then
        Rem formula entered
            If InStr(LCase(.FormulaR1C1), functionName) = 0 Then
                Rem non-ce formula entered, delete ce-Validation
                .Validation.Delete
            Else
                Rem new ce formula entered, update stored formula
                xVal = CellEntry(oneCell)
            End If
        Else
        Rem text entered
            If Application.CutCopyMode Then
                .Validation.Delete
            Else
                If InStr(LCase(.Validation.ErrorMessage), functionName) = 0 Then
                    Rem cell has non-CE validation
                Else
                    Rem set new value for CellEntry and replace formula in cell
                    .Validation.InputMessage = CStr(.Value)
                    Application.EnableEvents = False
                    .FormulaR1C1 = .Validation.ErrorMessage
                    Application.EnableEvents = True
                End If
            End If
        End If
    End With
Next oneCell
End Sub

If a spreadsheet formula involves the new function CellEntry(), entering text into that cell will not overwrite the formula. It will set the value returned by CellEntry(). For example:

Enter the formula =CellEntry() in a cell.
The cell will appear empty and the formula "=CellEntry()" will be in the formula bar
Enter "cat" into the cell
The cell now shows "cat", the value of the formula "=CellEntry()", which is shown in the formula bar.

To use it in your sitution. If you want a cell to have the formula =A1+1 unless it has been overwritten, put this formula in a cell

=IF(CellEntry()<>"",CellEntry(),A1+1)

If what has been typed in the cell is "", the formula returns A1+1.
When something has been typed into the cell, the formula returns what was typed.

NOTES:
If a cell has a formula that includes the CellEntry UDF:
a) entering a constant into the cell sets the value of CellEntry to that constant as text.

b) entering a formula into that cell changes the formula in the cell, but not its CellEntry value

c) clearing the contents of that cell sets the value of CellEntry() to vbNullString. It does not remove the formula from the cell.

To remove a CellEntry formula from a cell, either delete the cell or enter a non-CellEntry formula (eg "=3") and then delete that formula.

Copy/Pasteing a CellEntry formula cell also copies the CellEntry value.

Not realy.
Entering text into a cell overwrites any existing formula.

There are ways ease the restoration of formulas that have been overwritten (Defining a Name as a function can save typing), but VB is needed for it to be automatic.

First block of code in a normal module (See link)

http://www.contextures.com/xlvba01.html#Regular

Second block goes in ThisWorkbook
http://www.contextures.com/xlvba01.html#Workbook

Link has some basic in macro. More useful links below

http://www.bettersolutions.com/excel...R646543531.htm


VBA Noob

Charlie, welcome to the forum

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.