4
votes

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.

4
It is possible that, if you state why you want all as text, people give you better options. Just a suggestion...sancho.s ReinstateMonicaCellio
Good input! Two reasons: *I don't want decimals to be automatically deleted, *Values will be matched with text in other sheets and thus to be matched without any problem they should be textuser1283776
When you do the matching in the second sheet, is there any reason why you can't cast the values in sheet 1 to string during comparison?Vegard

4 Answers

3
votes

Easy...........pre-format the cell to Text, then have an Event macro monitor the cell and change the format to General if a formula is entered; and then force formula execution. For example cell 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
3
votes

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
1
votes

Conditional Formatting

Highlight range you want affected and click conditional formatting. Apply as shown below. You want to format cells not containing text "=" to "text".

enter image description here

0
votes

You can force Excel to interpret a number as a string by appending a single quote to the start of the number even if the cell format is set to General:

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