Cell_Changing event in Excel

Question

Is there something equivalent to Cell_Change Event in VBA or even VSTO that would fire BEFORE the user input is committed into the cell? I need to add some checks just before a new value is entered in a cell.

By the time the WorkSheet_Change event fires, Excel has already modified the user input and converted it into a different value. I need to get the original input that user typed.

EDIT

To be more specific, I want to prevent Excel from converting values like 3E9, which happens to be a valid (non-numeric/non-scientific) input in my case, to scientific notation.

These values can be input by the user in any cells in the entire worksheet, so I cannot pre-apply Text formatting because that will stop Excel from doing its normal functionality on valid numeric data too.

So I'm planning to:

• somehow grab the input just before it goes into the cell and gets converted.
• then check if it fits the exact pattern I'm watching for and apply Text format on that specific cell on-the-fly.

Answer 1

You could trap the scientific notation being applied instead and reverse it into a text string

Updated with a RegExp to handle the parsing

• Reformat valid values back to the initial text (i.e. 120E3 or 99E13)
• Replaces mangled values with a message to renter them (with the cell already formatted as text so next time 12E14 stays as text 12E14

sheet event code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim strTmp As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Pattern = "([1-9]+)(0+)"
    Application.EnableEvents = False
    For Each rng1 In Target.Cells
        If rng1.NumberFormat = "0.00E+00" Then
        rng1.NumberFormat = "@"
        strTmp = Len(.Replace(rng1.Value, "$2"))
            If .Test(rng1.Value) Then
            rng1.Value = "'" & .Replace(rng1.Value, "$1E") & strTmp
                Else
            rng1.Value = "Pls re-enter as text"
            End If
        End If
    Next
End With
Application.EnableEvents = True
End Sub

Answer 2

What about the undo function?

Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim OldValue As String, NewValue as string
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub 
    With Application 

        .EnableEvents = False 

        .Undo 
        'this is before
        OldValue = Target.Cells(1).Value 
        .Redo 
        'this is after 
        Newvalue = Target.Cells(1).value

        'do some check to see if you need to reverse the change 
        If oldvalue > newvalue then 
            .undo
            Msgbox "Not valid"
        end if

        .EnableEvents = True 

    End With

End Sub

Answer 3

I wonder if you can use Data Validation to help with this. Let's say you have valid inputs like 3E9 and 4E7, but you don't have valid inputs like 5E2 or 7E1. Then let's further assume you only rarely have valid numeric inputs above 10m. If this very specific situation is true, you could warn the user when they enter a number > 10m. If it's legitimate, they dismiss the warning and move on. If they intended to enter a literal string, they can cancel the input and follow your instructions to precede with an apostrophe. You could create DV like this:

Allow: Custom
Formula: =OR(ISTEXT(A1),A1<10^7)
Style: Warning
Title: Scientific Notation
Msg: If you enter a string like '9E99' Excel will convert to a number in scientific notation format. If you want the literal string, precede it with an apostrophe.

It may be asking a lot that your situation is that specific. Maybe there are some other characteristics that you could use DV to avoid. Like if it's extremely unlikely that someone will enter a whole number, you could DV to check for that.