Decimal numbers not recognized as numbers

[Stein Kristiansen]

I have a userform with a TextBox that enters data into the active cell. I
have also a validation of the data to check that only numbers are entered.

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNumeric(TextBox1) Then
MsgBox "Only numbers"
TextBox1 = vbNullString
Cancel = True
End If
End Sub

All this looks to work fine.

If the number entered is an integer the number is recognized as a number
(active cell is a part of SUM formula to verify this).
If the number in the TextBox1 is a decimal number like 123,45 (yes, I live
in a country that uses ,-comma as decimal symbol and regional settings
reflects this) the validation accept is as a number, but the active cell
does not see it as a number but as text (the SUM formula shows that it is
not seen as a number).
If I go to the spreadsheet, double click in the cell that contains 123,45
and then select another cell in the spreadsheet (make no changes to the
cell containing 123,45) THEN the spreadsheet recognize 123,45 as a number
and udates the result of the SUM formula.

I have tried to format the active cell after the number has been entered:

ActiveCell.NumberFormat = "# ##0.00;(# ##0.00)"

but this doesn't help.

All help will be appreciated.

 

[Jim Cone]

Stein,

Something to try, after verification of number...

Dim dblEntry as Double
...
dblEntry = CDbl(TextBox1.Value)
ActiveCell.Value = dblEntry

Regards,
Jim Cone
San Francisco, USA