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

 

[Tom Ogilvy]

Since Jim says something to try, perhaps some added.

cdbl should convert the number with respect to your regional settings.

When you let excel do the conversion implicitly as you do now (all entries
in a textbox are text), then it is interpreted as if it is a US English
value. It is put in the cell as Text. then when you edit the cell, it is
re-evaluated and converted to a number. Applying a number format to text
(as you have tried) does not convert what is stored there.

Stephen Bullen has graciously put his chapter on dealing with international
issues online:
http://www.oaltd.co.uk/ExcelProgRef/Ch22/default.htm

from his book:
Excel 2002 VBA Programmer's Reference

Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg
Published by Wrox Press, November 2001
ISBN: 0764543717


You might find this online resource an excellent reference when dealing with
these problems.

 

[Stein Kristiansen]

Dear Jim and Tom,

You saved my day!!! Cdbl fixes my problem. Thanks for the link to the web
page discussing international problems, I think I'll have to read it
several times to fully understand all the aspects.

Regards
Stein