CODEWITHSUNDEEP
exceluserformdecimal-pointvba
InternInNeed
InternInNeed

Reputation: 159

VBA dots from database get loaded into textbox as comma

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?

Upvotes: 2

Views: 2211

Answers (1)

GSerg
GSerg

Reputation: 78185

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.

Upvotes: 2

Related Questions