Confusion Around Application.DecimalSeparator

[MartinS]

OK, so I have a userform with a fair number of input controls (TextBoxes). To attempt to reduce the chance of users inputting invalid data, I have applied an KeyPress class event to validate the key entered, and where a decimal is allowed, append the decimal separator. What i am trying to do is deal with users who have different number settings for a decimal separator, i.e. in the Netherlands, where this application will be used, some have their Formats set to Dutch (Netherlands), which uses a comma for a decimal separator and a period for a thousands separator, but also some will use English (United States) which uses a period for the decimal separator and a comma for the thousands separator.
My plan was to determine if the user had the UseSystemSeparators option checked - if not, find out the symbols from a formatted number, and if they did, then take the Application.DecimalSeparator.
Whichever character is used is then appended to the 'allowed list' of characters. But what I am seeing is, irrespective of which country for Format is set, it sees the Application.DecimalSeparator as a comma - even if the workbook displays a period as the decimal separator. All I wanted it to do was reflect what Excel used, so am not sure where I went wrong!
My KeyPress event is as follows:

Private Sub TextBoxEvents_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim CtrlDataVal As ControlValidation
Dim strValidChars As String
Dim sDecimalSeparator As String
CtrlDataVal = GetControlData(TextBoxEvents.Name)
strValidChars = CtrlDataVal.ValidChars
'\* if the control has decimal places...
If CtrlDataVal.HasDecimals Then
    If Not Application.UseSystemSeparators Then
        sDecimalSeparator = Mid(Format(1000, "#,##0.00"), 6, 1)
        strValidChars = strValidChars & sDecimalSeparator
    Else
        strValidChars = strValidChars & Application.DecimalSeparator
    End If
End If
If Not strValidChars = "" Then KeyAscii = ValidateText(KeyAscii, strValidChars, False)
End Sub

For Reference, ControlValidation is a Type which contains properties for each control, such as minimum and maximum allowed values, what the allowed characters are (for numbers only) and whether the input allows decimal places.
I know by changing the code within the If statement to the code below, that should resolve the issue, but I'm at a loss as to why Excel and the userform display the same decimal separator, but do not allow input of the same character!

If CtrlDataVal.HasDecimals Then
   sDecimalSeparator = Mid(Format(1000, "#,##0.00"), 6, 1)
   strValidChars = strValidChars & sDecimalSeparator
End If

So at the moment (with the English (US) Format set), my numbers in Excel show as 1,000.00, and the values in my userform display in the same format, but when I attempt to input, say, 2.5 into a textbox, I can only enter 2,5 which is accepted and the form inputs can be saved back to the workbook without issue.
Can anyone she light on why the original code doesn't use the correct decimal separator when Excel is set up to mirror the system?
Many thanks
Martin

 

[Rick Rothstein]

Thanks, though isn't Format a quicker way to achieve the same?

sDecimalSeparator = Mid(Format(1000, "#,##0.00"), 6, 1)

That would tell you the decimal separator the computer is using which is not necessarily the same as what Excel using... what I proposed should tell you what Excel itself is using.

 

[MartinS]

That would tell you the decimal separator the computer is using which is not necessarily the same as what Excel using... what I proposed should tell you what Excel itself is using.

OK, thanks.

 

[MartinS]

Just validate on tab change and change the backcolour of the textboxes to make them easy to spot.

You could also take a leaf out of the web design book and put the validation message next to the offending control

What I'm doing already is marking inputs that have changed since they were loaded, so it may not take much work to validate them at the same time...
Not enough room for messages, it's a bit of a squeeze!

Thanks again