How to work with European decimal numbers in text boxes on User Form in Excel. Watch the video below:

Europeans use the comma instead of the dot in decimal numbers. One of the main reasons is that their numerical keypad has only a comma. Therefore, finance professionals who tend to enter decimal values via the numerical keyboard use the comma.

If we have huge European decimal numbers like statistical data, we can easily convert them to standard dot decimal numbers using the ‘NUMBERVALUE’ formula as shown below:

You can read more about the NUMBERVALUE function here.

Another way to handle European decimal numbers in an Excel worksheet is to disable the ‘Use system separators’ by clicking on File –> Options –> Advanced as shown in the images below:

For example, if we create a workbook in the USA and share it with someone in Denmark then the user in Denmark will see the numbers according to his Regional settings in Windows.
If the display of the decimal numbers doesn’t happen automatically then it only means that we have configured Excel to ignore the ‘Use system separators’ as shown below.

Now handling European decimal numbers in text boxes on a user-form and transferring them to an Excel worksheet becomes very interesting, especially if we wish to perform calculations with the European decimal numbers. We’ll use the concepts above in our macro that automates the complete process of transferring European decimal numbers from UserForm text boxes into an Excel worksheet:

Private Sub Workbook_Activate()
Application.UseSystemSeparators = False
End Sub

Private Sub Workbook_Deactivate()
Application.UseSystemSeparators = True
End Sub

Sub ShowUserForm()
UserForm1.Show
End Sub

Private Sub CommandButton1_Click()
Dim CommaPos As Long, NumCharsAfterComma As Long

CommaPos = InStr(TextBox1.Text, “,”)

‘MsgBox CommaPos

NumCharsAfterComma = Len(Mid(TextBox1, CommaPos + 1))

If NumCharsAfterComma > 2 Then
MsgBox “You’ve entered too many numbers after the commain TextBox1. Only two numbers allowed.”
Exit Sub
End If

Range(“B1”) = TextBox1

Range(“B1”) = Range(“B1”) * 0.01

CommaPos = InStr(TextBox1.Text, “,”)

NumCharsAfterComma = Len(Mid(TextBox2, CommaPos + 1))

If NumCharsAfterComma > 2 Then
MsgBox “You’ve entered too many numbers after the comma in TextBox2. Only two numbers allowed.”
Exit Sub
End If
Range(“B2”) = TextBox2
Range(“B2”) = Range(“B2”) * 0.01

Range(“B3”) = Range(“B1”) * Range(“B2”)
Range(“B1:B3”).NumberFormat = “0.00”
TextBox3 = Range(“B3”).Text

End Sub