CODEWITHSUNDEEP
vbaexcelnumber-formatting
user1283776
user1283776

Reputation: 21764

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

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

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.

Upvotes: 4

Views: 582

Answers (4)

Siddharth Rout
Siddharth Rout

Reputation: 149295

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

Upvotes: 3

Ronnie Smith
Ronnie Smith

Reputation: 18565

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

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 3

Comintern
Comintern

Reputation: 22195

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")

Upvotes: 0

Related Questions