Excel VBA - Automatically add "=" when user input numbers and arithmetic operations but taking care of dates

Question

In order to avoid the user typing = before they enter an arithmetic operation in a cell (i.e. 5+2, 8*8, etc) I have this code:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, Range("C4:C303")) Is Nothing Then

    If Target = "" Then Exit Sub

    Application.EnableEvents = False
    
    TempTarget = Target

    Target = "=" & TempTarget
    
    If IsError(Target) Then Target = TempTarget

    Application.EnableEvents = True

End If

On Error GoTo 0

End Sub

It works perfectly for additions (5+5) and multiplications (9*55), but doesn't work well for divisions and subtractions with small numbers (5-2, 8/9) because Excel treats them as dates. It works well for subtractions and divisions with bigger numbers (that couldn't be dates). I want to have in the cell the arithmetic formula but displaying the result. How can I fix the code so that it always work the way I want?

Answer 1

This isn't as straight-forward to solve as it might appear. If you enter values like 5-2 or 8/9 there's no way to intercept the change taking place to the cell before Excel changes it into a Date format.

However there is a solution, although it may not suit your purpose.

1. Set the Format of each of the cells you want to apply this to (presumably C4:C303) to "Text" or "@".

2. Change your code to include the addition of Target.NumberFormat = "General" before setting the value of the cell.

The code to achieve that would look like this:

...
Application.EnableEvents = False
TempTarget = Target

' Add this line to change the format back to General
Target.NumberFormat = "General"

Target = "=" & TempTarget
...

This will then correctly handle cases like 5-2 or 8/9 that would otherwise become dates automatically.

Side Effect

This creates an undesired side effect that if one of the existing calculated cells is edited again later it will now be in "General" format instead of text.

This could be resolved by using the SelectionChange event and by setting the format of the cell to "Text" or "@" again if it's inside the range you are working in, but you'd want to undo that if the user doesn't edit the value of the cell.

It's a bit messy. Excel clearly doesn't lend itself to this type of approach.