Numberformat that allows me to enter formulas but stores values as text?

Question

Is it possible to set the numberformat in a cell/column using either Excel or VBA so that:

• if I enter a formula (anything starting with =) Excel will calculate the formula (and not interpret it as just text)
• if I enter a value, for example 5.80, Excel will store it as text?

I'm having a problem where I want all user input to be stored as text, but users should also be able to enter formulas. If I set the numberformat to text, formulas aren't interpreted. If I set the numberformat to general, values are stored as numbers.

Answer 1

Here is my version.

Format all cells in that sheet as Text. This code uses Application.Evaluate() to evaluate all formulas and store the result as text.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim aCell As Range

    On Error GoTo Whoa

    Application.EnableEvents = False

    '~~> You need this in case user copies formula
    '~~> from another sheet
    Target.Cells.NumberFormat = "@"

    '~~> Looping though all the cells in case user
    '~~> copies formula from another sheet
    For Each aCell In Target.Cells
        If Left(aCell.Formula, 1) = "=" Then _
        aCell.Value = Application.Evaluate(aCell.Formula)
    Next

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub