VBA dots from database get loaded into textbox as comma

Question

I know the Headline sounds odd so I will start off with a screenshot:

As you can see, the problem is that the point suddenly changes to a comma when I look up an ID in the UserForm.

Before recalling Infos, I am saving all Information rather straightforward:

with ws
    Range("BH" & lastRow).value = Me.payinfoOnTime
    Range("BI" & lastRow).value = Me.payinfo30
    Range("BJ" & lastRow).value = Me.payinfo60
    Range("BK" & lastRow).value = Me.payinfo90
    Range("BL" & lastRow).value = Me.payinfo90more
End with

Recalling the respective info for a searched ID is done by:

Set FoundRange = ws.Range("D4:D500").Find(What:=Me.SearchSuppNo, LookIn:=xlValues)

With ws
    Me.SEpayinfoontime = FoundRange.Offset(0, 56)
    Me.SEpayinfo30 = FoundRange.Offset(0, 57)
    Me.SEpayinfo60 = FoundRange.Offset(0, 58)
    Me.SEpayinfo90 = FoundRange.Offset(0, 59)
    Me.SEpayinfo90more = FoundRange.Offset(0, 60)
end with

The Problem is that later calculations for scores are depending on those textboxes and I constantly get an error, unless I always manually change the commas back to points.

Any ideas how I can fix this?

But it get's loaded into the TextBox with a comma and from this TextBox entries are edited and calculations performed. So it would Need to be changed before it is loaded into the TextBox. That I don't know how to do... — InternInNeed, Sep 01 '16 at 11:52
I might be wrong here but this seems like a Regional Format definition. If that's the case, can't you simply change the Decimal separator to be `.` from the control panel? — Victor Moraes, Sep 01 '16 at 11:59
It's a regional settings problem. If you know your decimal separator(s), you can simply change it to the preferred separator each time you write/read the text box. btw, you don't use your `with` variables in both examples. In the first you'd have to use `.Range(...` and in the second it's not needed at all. — arcadeprecinct, Sep 01 '16 at 12:06
I am sorry, which control panel do you mean? The Decimal Separator within Excel is a dot. — InternInNeed, Sep 01 '16 at 12:06
@arcadeprecinct those are snips from two different modules, but thank you. How can I achieve this changing of preferred Separators when I fill a textbox? I apologize but I am still new to VBA — InternInNeed, Sep 01 '16 at 12:08
As Cody G mentioned, just use `Replace` on the string to change `,` into `.`. The excel separator is pretty irrelevant I think. It's more about the systems local settings. — arcadeprecinct, Sep 01 '16 at 12:14
If you store numbers as numeric data types in the database, it should not concern you how they appear in the user interface. If it does, you are doing something wrong - either storing numbers as strings in the database, or trying to operate on locale-dependent textual representations in a locale-independent fashion or vice versa. If you want to let the user use the Windows settings they selected, which you should, use locale-dependent functions to convert your textboxes' contents to numbers before calculating (e.g. `CDbl`, `CLng` etc) as opposed to locale-independent functions (e.g. `Val`). — GSerg, Sep 01 '16 at 12:36
@GSerg Thank you for your extensive reply. I am not working with strings or operating with...whatever you said haha...I am to new to this to even know. I am just working with a UserForm where you can look up Infos belonging to an ID and then edit it when needed. Now, if something is edited, recalculations on other Worksheets are needed. However, if everytime I look up Infos the Point from the worksheet is replaced by a comma, an type mismatch error occurs because it's not a number anymore. Does my explanation make anything clearer? ^^ — InternInNeed, Sep 01 '16 at 12:47
A Runtime Error 13 "Type Mismatch" occurs at the Point where I want to save a certain score based on what is in the TextBox: `Range("AM" & DatabaseRow).value = Int(Me.SEpayinfoontime) / 10` — InternInNeed, Sep 01 '16 at 13:00
`Int` expects a number. If you give it a string, it will be automatically converted to a number first. Automatic conversion is done with respect to the system locale. If you have a comma in the textbox and automatic conversion fails to convert that, then your system locale decimal dot is not a comma. That means you should look at code that populates textboxes, and check if the system decimal dot is the same as `Application.International(xlDecimalSeparator)`. — GSerg, Sep 01 '16 at 13:11
I changed it as you said but still got the same error...thank you though! The answer below solved the Problem :) — InternInNeed, Sep 01 '16 at 13:12
How would I check whether the System decimal dot is the same? The populating code is really just `Me.SEtextboxname = FoundRange.Offset(0, number)` for over 200 Textboxes — InternInNeed, Sep 01 '16 at 13:15
In the immediate window, execute `? CStr(42.42)` and `? Str(42.42)`. — GSerg, Sep 01 '16 at 13:21

score 2 · Accepted Answer · edited May 23 '17 at 10:32

The line:

Me.SEpayinfoontime = FoundRange.Offset(0, 56)

is in fact:

Me.SEpayinfoontime.Value = FoundRange.Offset(0, 56).Value

When you populate an MSForms.TextBox using the .Value property (typed As Variant), like you implicitly do, and providing a number on the right side, the compiler passes the value to the TextBox as a number, and then the value is automatically converted to string inside the TextBox.

Exactly how that conversion happens does not appear to be documented, and from experiment, it would appear there is a problem with it.

When you freshly start Excel, it would appear assigning .Value will convert the number using the en-us locale, even if your system locale is different. But as soon as you go to the Control Panel and change your current locale to something else, .Value begins to respect the system locale, and changes its result depending on what is currently selected.

It should not be happening and I would see it as an Excel bug.

But if you instead assign the .Text property, the number is converted to string using the current system decimal dot, and that conversion happens outside of the TextBox, because the compiler knows .Text is a string, so it converts the right-hand side number to string beforehand.

So in your situation I would:

Make sure I always use the .Text property explicitly:
```
Me.SEpayinfoontime.Text = ...
```
Make sure I explicitly use the correct kind of functions to convert between text and numbers:
```
Me.SEpayinfoontime.Text = CStr(FoundRange.Offset(0, 56).Value)

MsgBox CInt(Me.SEpayinfoontime.Text) / 10
```
although this step is optional and represents my personal preference. Given that it's a string on the left side of the assignment, VB will use CStr automatically.
Go to Excel's settings to make sure the "Use system separators" tick is set.
Check what locale is selected in the Control Panel - Language and Regional settings.
- If it is not En-Us, I would select En-Us to make sure the decimal separator is a dot there.
Restart Excel.

Wow, what an extensive answer again. Would I also Need to Change something to the way I am saving the Input when an ID is first created? `ws.Range("BH" & lastRow).value = Me.payinfoOnTime` — InternInNeed, Sep 01 '16 at 14:32
Yes, you would: `ws.Range("BH" & lastRow).value = CDbl(Me.payinfoOnTime.Text)`. — GSerg, Sep 01 '16 at 14:35

VBA dots from database get loaded into textbox as comma

1 Answers1

Linked