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

Question

Is it possible to set the number format in a cell / column using 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 like 5.80, does Excel save it as text?

I had a problem when I want all user input to be saved as text, but users also had to enter formulas. If I set the numberformat value in the text, the formulas will not be interpreted. If I set the value of numberformat as a whole, the values ​​are stored as numbers.

Answer 1

Here is my version.

Text. Application.Evaluate() .

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

Answer 2

........... Text, , , General, ; . , B9:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim B9 As Range
    Set B9 = Range("B9")
    If Intersect(Target, B9) Is Nothing Then Exit Sub

    Application.EnableEvents = False
    With B9
        If Left(.Value, 1) = "=" Then
            .NumberFormat = "General"
            .Value = .Value
        Else
            .NumberFormat = "@"
        End If
    End With
    Application.EnableEvents = True
End Sub

Answer 3

, , . , . , "=", "".

Answer 4

Excel , , :

ActiveSheet.Cells(1, 1).Value = "'5.80"
'...or...
ActiveSheet.Cells(2, 1).Value = "'" & Format$(58 / 10, "#.00")