Dots removed when data is send to sheet from userform.

[Mathijs3]

Dear all,

my userform sends the data that the user enters to a sheet, but for some reason suddenly all dots are removed once the data is send.

I haven't gotten a clue yet where to look in my formula's, but here is a part of the formula which is about the textbox in question. (the form has 36 of these..)
its about the kind of input that's allowed.

Private Sub tbNieuwV1_Keypress(ByVal KeyAscii As MSForms.ReturnInteger) 
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Then
    KeyAscii = KeyAscii
    Else: KeyAscii = 0: MsgBox ("Invalid entry, only use numbers. For decimals use dots(.)"): End If

End Sub

the relevant part of the code that sends the data to the sheet looks like this:

    Dim Regis As String
    Regis = Sheets("Uitvoertabel").Range("B" & Rows.Count).End(xlUp).Row

    Sheets("Uitvoertabel").Cells(Regis + 1, "BN").Value = tbNieuwV1.Value - tbActueelV1.Value
    Sheets("Uitvoertabel").Cells(Regis + 1, "BQ").Value = tbActueelV1.Value

It pretty much sends the old stock minus the new stock to the sheet, as well as the new stock. I need this info for Pivot tables.
A lot more data is send in this part of the formula, but i thought this would be the only relevant part.

Maybe I messed up some settings or something, but i don't even know where to begin looking right now.. Any help would be greatly appreciated!

[Greg M]

Hi there,

Your code uses a dot as a decimal separator, but is this consistent with the Regional Settings on your Windows installation?

Your code worked correctly on my installation with Regional Settings selected as English(United Kingdom), but when I changed this setting to Dutch(Netherlands) it produced the error that you're seeing.

The Regional Settings can be changed via Start >> Control Panel >> Change display language >> Format > select required language.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[Mathijs3]

Hi Greg,

Thank you for your help!

I changed the settings to UK for a try, and that worked perfectly. Problem was, that i'd have to do that with every computer that the userform will be used on, so i just changed the code to comma's instead of dots. Works as well, or do i overlook a potential problem here?


Mathijs

[Greg M]

Hi again Mathijs,

Many thanks for your feedback and also for the Reputation increase - much appreciated

I don´t think you´re overlooking anything, but I´m away from my computer & Excel for the next two weeks, so I´m not able to do any checks or tests.

Let me know if you have any further problems & I´ll see what i can do when I get back.

Best regards,

Greg M

[Mathijs3]

Hi Greg,

thank you for getting back to me.

I had a few more problems but with some help from a colleague I solved it. I couldn't set all the computers to the same settings, because all those computers use more than just this file. To avoid damage to the other programs, I used the following work-around.

Public Sub ChangeSystemSeparators()

Application.UseSystemSeparators = False
Application.DecimalSeparator = "."
Application.ThousandsSeparator = ","

End Sub

I basically declared the separators for the entire file in all relevant forms and sheets, so that it overrules the standard setting for the pc.
Now, it uses dots in VBA, and comma's in the 'normal' worksheets. Then, i used this part of code to define all values that were transferred from the sheet to the userform.

Private Sub tbNieuwV1_Change()
   If tbNieuwV1 = vbNullString Then
    Exit Sub
    Else
    tbNieuwV1 = Replace(tbNieuwV1, ",", ".")
    End If
End Sub

Also, i defined a few variables in the rest of the code, to force a certain formatting on them.

Textbox.Value = Format(Textbox.Value("#####0.0###"))

Now it all seems to work!

- Edit: marked thread as solved -

[Greg M]

Hi again,

Many thanks for that feedback - it´s always interesting and useful to receive follow-up information about a proposed solution.

Best regards,

Greg M