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

 

[mole999]

have you changed your system language to test this`(French, german etc) i'm pretty sure its to do with what is installed and configured and not what is tested on the base system

 

[RoryA]

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.

I think you have that back to front. Application.DecimalSeparator is only relevant if the user doesn't have the UseSystemSeparators option checked.

It would, IMO, be much easier to let the user type what he likes and then validate it using CDbl if it should be a number.

 

[MartinS]

have you changed your system language to test this`(French, german etc) i'm pretty sure its to do with what is installed and configured and not what is tested on the base system

No, I was basing my assumption on the fact that Excel had changed to reflect the selected country Format, so did not change the language.

I think you have that back to front. Application.DecimalSeparator is only relevant if the user doesn't have the UseSystemSeparators option checked.

It would, IMO, be much easier to let the user type what he likes and then validate it using CDbl if it should be a number.

Ah, OK, that makes sense, although my revised solution to simply use what Excel uses as a separator works fine, but it's good to know why I couldn't get it to do what I expected it!

But that then means I have to also check to see if it contains a decimal place and drop it (if not), and doesn't that also mean I have to create an afterupdate event for each control (there are a lot of them over five tabs) to call the validation routine? I went for the keypress validation approach as it doesn't need me to create events for the appropriate controls, the class handles calling my single event at runtime.

Thanks
Martin

 

[RoryA]

My preference is to only properly validate the data before submitting it, not on every keystroke or after each control is filled out. But I don't see the issue with using CDbl - it will try and convert based on the user's regional settings automatically.

 

[MartinS]

My preference is to only properly validate the data before submitting it, not on every keystroke or after each control is filled out. But I don't see the issue with using CDbl - it will try and convert based on the user's regional settings automatically.

Thanks.

Whilst I agree, when using a multi-tab approach, it makes it time consuming for the user to go through each tab to find the items that have failed validation, so for this solution, I feel limiting the input by control is going to receive less negative feedback from users, but will have to wait and see I guess

 

[Rick Rothstein]

I think this should work... just "ask" the Excel worksheet what the decimal point is.

Function GetDecimalPoint() As String
  Dim CellText As String, UnusedCell As Range
  Dim LastUsedRow As Long, LastUsedCol As Long
  LastUsedRow = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row
  LastUsedCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
  Set UnusedCell = Cells(LastUsedRow, LastUsedCol)
  Application.ScreenUpdating = False
  UnusedCell.Value = "1,234.56"
  CellText = UnusedCell.Text
  GetDecimalPoint = Mid(CellText, Len(CellText) - 2, 1)
  UnusedCell.Clear
  Application.ScreenUpdating = True
End Function

Similarly, for the thousands separator (which might be a space instead of a dot or comma)...

Function GetThousandsSeparator() As String
  Dim CellText As String, UnusedCell As Range
  Dim LastUsedRow As Long, LastUsedCol As Long
  LastUsedRow = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row
  LastUsedCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
  Set UnusedCell = Cells(LastUsedRow, LastUsedCol)
  Application.ScreenUpdating = False
  UnusedCell.Value = "1,234.56"
  CellText = UnusedCell.Text
  GetThousandsSeparator = Mid(CellText, 2, 1)
  UnusedCell.Clear
  Application.ScreenUpdating = True
End Function

 

[RoryA]

Well you can still use this approach with CDbl if you wish.

 

[MartinS]

I think this should work... just "ask" the Excel worksheet what the decimal point is.

Function GetDecimalPoint() As String
  Dim CellText As String, UnusedCell As Range
  Dim LastUsedRow As Long, LastUsedCol As Long
  LastUsedRow = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row
  LastUsedCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
  Set UnusedCell = Cells(LastUsedRow, LastUsedCol)
  Application.ScreenUpdating = False
  UnusedCell.Value = "1,234.56"
  CellText = UnusedCell.Text
  GetDecimalPoint = Mid(CellText, Len(CellText) - 2, 1)
  UnusedCell.Clear
  Application.ScreenUpdating = True
End Function

Similarly, for the thousands separator (which might be a space instead of a dot or comma)...

Function GetThousandsSeparator() As String
  Dim CellText As String, UnusedCell As Range
  Dim LastUsedRow As Long, LastUsedCol As Long
  LastUsedRow = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row
  LastUsedCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
  Set UnusedCell = Cells(LastUsedRow, LastUsedCol)
  Application.ScreenUpdating = False
  UnusedCell.Value = "1,234.56"
  CellText = UnusedCell.Text
  GetThousandsSeparator = Mid(CellText, 2, 1)
  UnusedCell.Clear
  Application.ScreenUpdating = True
End Function

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

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

Well you can still use this approach with CDbl if you wish.

Sounds interesting, but think am too far along the development process to change it all now, but would be interested to know how for future?

Thanks again

Martin

 

[Kyle123]

Sounds interesting, but think am too far along the development process to change it all now, but would be interested to know how for future?

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