Minor formatting issue: allow capturing a (.) full-stop as decimal

[onmyway]

Hi guys,

First off, I have posted this question on another forum, but have been unable to get a response. I am hoping that someone here might be able to shed some light!

I believe it should be a minor modification in my code.

I make use of the following code to format my fields to only allow for Numeric values, and to format it to this format, "#,##0.00".

However, the frustration with this is that I can only capture my decimals making use of a (,) comma, and not a (.) full-stop. It is much easier to capture making use of a Numpad, and the (.) dot function.

I still want to keep my formatting, but would like to be able to capture my decimal by either using a (.) dot or (,) comma.

Here is my code:


Private Sub txtStockSOS1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

' Run the Error handler "ErrHandler" when an error occurs.
On Error GoTo Errhandler

If Not IsNumeric(txtStockSOS1.Value) And txtStockSOS1.Value <> vbNullString Then

MsgBox "Sorry, only numbers allowed"

txtStockSOS1.Value = vbNullString

Cancel = True

Else

If Trim(Me.txtStockSOS1.Value) = "" Then

MsgBox "Please enter a value"

Cancel = True

Else
txtStockSOS1.Value = Format(txtStockSOS1.Value, "#,##0.00")

End If

End If

SOS1 = txtStockSOS1.Value

EOS1 = SOS1 + Receive1 - Lost1 - Broken1 - Worn1

Me.txtStockEOS1 = Format(EOS1, "#,##0.00")

'txtStockEOS1.Value = Format(txtStockEOS1.Value, "#,##0.00")

Errhandler:

End Sub

Thank you in advance!

[gmayor]

Can you explain (with examples) what you mean by the bold text section. Decimal and thousand separators are determined by the Windows regional settings.

[onmyway]

Hi gmayor,

I think you have solved half my problem! :)

i did change my Regional settings Decimal to (.) and it worked. But, my concern is this: If i have this applications used on multiple systems and User PC, not all will have the same Regional Decimal settings. This might cause issues with calculations I run via VBA or Excel, especially when sheets are merged.

Will i be able to calculate a row of numbers stored as text, using VBA, where both decimal separators are used?

[gmayor]

You can evaluate the regional settings with a standard function and format accordingly e.g.



Option Explicit
#If Win64 Then
Private Declare PtrSafe Function GetLocaleInfo Lib _
"kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, _
ByVal LCType As Long, ByVal lpLCData As String, _
ByVal cchData As Long) As Long
#Else
Private Declare Function GetLocaleInfo Lib _
"kernel32" Alias "GetLocaleInfoA" _
(ByVal Locale As Long, _
ByVal LCType As Long, ByVal lpLCData As String, _
ByVal cchData As Long) As Long
#End If
Private Const LOCALE_USER_DEFAULT = &H400
Private Const LOCALE_SDECIMAL As Long = &HE
Private Const LOCALE_ILDATE As Long = &H22
Private Const LOCALE_ICOUNTRY As Long = &H5
Private Const LOCALE_SENGCOUNTRY = &H1002 ' English name of country
Private Const LOCALE_SENGLANGUAGE = &H1001 ' English name of language
Private Const LOCALE_SNATIVELANGNAME = &H4 ' native name of language
Private Const LOCALE_SNATIVECTRYNAME = &H8 ' native name of country

Private Function GetInfo(ByVal lInfo As Long) As String
Dim Buffer As String
Dim Ret As String
Buffer = String$(256, 0)
Ret = GetLocaleInfo(LOCALE_USER_DEFAULT, lInfo, Buffer, Len(Buffer))
If Ret > 0 Then
GetInfo = Left$(Buffer, Ret - 1)
Else
GetInfo = ""
End If
lbl_Exit:
Exit Function
End Function

Private Sub txtStockSOS1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' Run the Error handler "ErrHandler" when an error occurs.
On Error GoTo Errhandler
If Not IsNumeric(txtStockSOS1.Value) And txtStockSOS1.Value <> vbNullString Then
MsgBox "Sorry, only numbers allowed"
txtStockSOS1.Value = vbNullString
Cancel = True
Else
If Trim(Me.txtStockSOS1.Value) = "" Then
MsgBox "Please enter a value"
Cancel = True
Else
If Asc(GetInfo(&HE)) = 46 Then
txtStockSOS1.Value = Format(txtStockSOS1.Value, "#,##0.00")
ElseIf Asc(GetInfo(&HE)) = 44 Then
txtStockSOS1.Value = Format(txtStockSOS1.Value, "#.##0,00")
End If
End If
End If
SOS1 = txtStockSOS1.Value
EOS1 = SOS1 + Receive1 - Lost1 - Broken1 - Worn1
If Asc(GetInfo(&HE)) = 46 Then
Me.txtStockEOS1 = Format(EOS1, "#,##0.00")
ElseIf Asc(GetInfo(&HE)) = 44 Then
Me.txtStockEOS1 = Format(EOS1, "#.##0,00")
End If
'txtStockEOS1.Value = Format(txtStockEOS1.Value, "#,##0.00")
Errhandler:
Exit Sub
End Sub

[onmyway]

Hi gmayor,

Thank you for your comprehensive answer!

If I understand your code and method correctly, my value is formatted according to my regional settings (?), and this does indeed happen when running your code. However, how can I use it to force a certain format? As far as my testing showed, it is still my Regional settings that determined the format. Is this correct?

Or perhaps better yet, is there a way I can run a function with my UserForm_Initialize that can update my System Regional Settings to "English South Africa"? That way I will be certain that all my fields will format accordingly.

[onmyway]

Hi Graham,

I found this as possible solution:

http://www.mrexcel.com/forum/excel-questions/41533-controlling-windows-regional-settings-visual-basic-applications.html


Private Declare Function SetLocaleInfo _
Lib "kernel32" Alias "SetLocaleInfoA" ( _
ByVal Locale As Long, _
ByVal LCType As Long, _
ByVal lpLCData As String) As Boolean

Private Declare Function GetUserDefaultLCID% Lib "kernel32" ()

Private Const LOCALE_SDECIMAL = &HE

Private Sub ChangeSettingExample()
'change the setting of the character displayed as the decimal separator.
Call SetLocalSetting(LOCALE_SDECIMAL, ",") 'to change to ","
'check your control panel to verify or use the
'GetLocaleInfo API function
Stop
Call SetLocalSetting(LOCALE_SDECIMAL, ".") 'to back change to "."
End Sub

Private Function SetLocalSetting(LC_CONST As Long, Setting As String) As Boolean
Call SetLocaleInfo(GetUserDefaultLCID(), LC_CONST, Setting)
End Function
This seems to work for temporarily changing the decimal as needed.

One question though: how can I change the code to return to the system default decimal after application ran? I.e., If a system was already using (.), it must not change it to (,) and vice-versa.

So, check what the system decimal is, and assign that to a variable, and change it back.

[onmyway]

You can evaluate the regional settings with a standard function and format accordingly e.g.



Option Explicit
#If Win64 Then
Private Declare PtrSafe Function GetLocaleInfo Lib _
"kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, _
ByVal LCType As Long, ByVal lpLCData As String, _
ByVal cchData As Long) As Long
#Else
Private Declare Function GetLocaleInfo Lib _
"kernel32" Alias "GetLocaleInfoA" _
(ByVal Locale As Long, _
ByVal LCType As Long, ByVal lpLCData As String, _
ByVal cchData As Long) As Long
#End If
Private Const LOCALE_USER_DEFAULT = &H400
Private Const LOCALE_SDECIMAL As Long = &HE
Private Const LOCALE_ILDATE As Long = &H22
Private Const LOCALE_ICOUNTRY As Long = &H5
Private Const LOCALE_SENGCOUNTRY = &H1002 ' English name of country
Private Const LOCALE_SENGLANGUAGE = &H1001 ' English name of language
Private Const LOCALE_SNATIVELANGNAME = &H4 ' native name of language
Private Const LOCALE_SNATIVECTRYNAME = &H8 ' native name of country

Private Function GetInfo(ByVal lInfo As Long) As String
Dim Buffer As String
Dim Ret As String
Buffer = String$(256, 0)
Ret = GetLocaleInfo(LOCALE_USER_DEFAULT, lInfo, Buffer, Len(Buffer))
If Ret > 0 Then
GetInfo = Left$(Buffer, Ret - 1)
Else
GetInfo = ""
End If
lbl_Exit:
Exit Function
End Function

Private Sub txtStockSOS1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' Run the Error handler "ErrHandler" when an error occurs.
On Error GoTo Errhandler
If Not IsNumeric(txtStockSOS1.Value) And txtStockSOS1.Value <> vbNullString Then
MsgBox "Sorry, only numbers allowed"
txtStockSOS1.Value = vbNullString
Cancel = True
Else
If Trim(Me.txtStockSOS1.Value) = "" Then
MsgBox "Please enter a value"
Cancel = True
Else
If Asc(GetInfo(&HE)) = 46 Then
txtStockSOS1.Value = Format(txtStockSOS1.Value, "#,##0.00")
ElseIf Asc(GetInfo(&HE)) = 44 Then
txtStockSOS1.Value = Format(txtStockSOS1.Value, "#.##0,00")
End If
End If
End If
SOS1 = txtStockSOS1.Value
EOS1 = SOS1 + Receive1 - Lost1 - Broken1 - Worn1
If Asc(GetInfo(&HE)) = 46 Then
Me.txtStockEOS1 = Format(EOS1, "#,##0.00")
ElseIf Asc(GetInfo(&HE)) = 44 Then
Me.txtStockEOS1 = Format(EOS1, "#.##0,00")
End If
'txtStockEOS1.Value = Format(txtStockEOS1.Value, "#,##0.00")
Errhandler:
Exit Sub
End Sub

Hi Graham,

I realize your code is based on the functionality of that I shared as found on mrexcel - just more advance, and in a sense better, as it includes Win64 functionality.



i was hoping you could perhaps show me how I can use this functionality to display the current user settings in a msgBox
Set the current user default decimal setting in a variable, change it to what i require, and change it back after use. Something like this:


Use this function:


Private Function SetLocalSetting(LC_CONST As Long, Setting As String) As Boolean Call SetLocaleInfo(GetUserDefaultLCID(), LC_CONST, Setting)
End Function

Call this if the current user default is not (.):


Call SetLocalSetting(LOCALE_SDECIMAL, ".")

Change it back to (,) after use:


Call SetLocalSetting(LOCALE_SDECIMAL, ",")

[Paul_Hossler]

Would FormatCurrency() and FormatNumber() help? They are both Regionalized



English South Africa
R 1 234 567,89
123 456.79
English (US)
$1,234,567.89
123,456.79






Option Explicit
Sub test()
Debug.Print "English South Africa"

Debug.Print FormatCurrency(1234567.89)
Debug.Print FormatNumber(123456.789)

'stop and change regional setting

Debug.Print "English (US)"

Debug.Print FormatCurrency(1234567.89)
Debug.Print FormatNumber(123456.789)


End Sub





Failing that Application.International () has access to almost everything you might need

[onmyway]

Hi Paul,

Thank you for your feedback and input. You have been very helpful!

I think I have nearly the solution.

The following seems to work. It allows the capturing of both decimals, and my value I then format to have a (,) as decimal. http://www.mrexcel.com/forum/images/smilies/icon_smile.gif

May i ask that you perhaps throw your eyes over the code to scrutinize it.

Also, how can i edit this to work for Office 64-bit (incorporate gmayor's method)?


Private Declare Function SetLocaleInfo _
Lib "kernel32" Alias "SetLocaleInfoA" ( _
ByVal Locale As Long, _
ByVal LCType As Long, _
ByVal lpLCData As String) As Boolean

Private Declare Function GetUserDefaultLCID% Lib "kernel32" ()
Private Const LOCALE_SDECIMAL = &HE



Private Function SetLocalSetting(LC_CONST As Long, Setting As String) As Boolean
Call SetLocaleInfo(GetUserDefaultLCID(), LC_CONST, Setting)
End Function


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim DecSep As String

DecSep = Application.International(xlDecimalSeparator)

MsgBox DecSep

If DecSep <> "." Then Call SetLocalSetting(LOCALE_SDECIMAL, ".")

On Error GoTo Errhandler

If Not IsNumeric(TextBox1.Value) And TextBox1.Value <> vbNullString Then

MsgBox "Sorry, only numbers allowed"

TextBox1.Value = vbNullString

Cancel = True

Else

If Trim(Me.TextBox1.Value) = "" Then

MsgBox "Please enter a value"

Cancel = True

Else
TextBox1.Value = Format(TextBox1.Value, "#,##0.00")
TextBox1.Value = Replace(TextBox1.Value, ".", ",")

End If

End If

Call SetLocalSetting(LOCALE_SDECIMAL, "DecSep")

Errhandler:

End Sub

Thank you!

[Paul_Hossler]

I believe that you are making too much work for yourself

If I understand, your UF will be used in English (US) and English (SA). I assume that the regional settings in control panel will be correct.


UF Textboxs deal with Strings

So in the US, the user will enter 123456.789 for $123,456.79

In SA, the user will enter 123456.789 for R 123 456,79

Excel stores that data as a Double (which is WRS independent)

Use CCur( ) to make the input a Currency (Use On Error if it can't be converted

The display of the Double on a worksheet can be handled by NumberFormat (see screen shot) and the display in a string for a Msgbox can be handled by FormatCurrency()

Combining a US and a SA worksheet should just paste the values in and the receiving WB's Locale should control the format




Option Explicit
Sub test()
Dim X As Double
On Error GoTo Oops

X = CCur("$123,456.789")

MsgBox X
MsgBox "Change regional settings"
X = CCur("R 123 456,789")

MsgBox X

Exit Sub

Oops:
MsgBox "Oops"

End Sub




In the screen shot, I entered 123456.789 when set to US settings and formatted as Currency ($123,456.79)

Changing to SA, the fact that Excel was told 'This is Currency' Excel will now format and display using SA conventions (R123 456,79)

The NumberFormat currency handles the hard work. Note that the internal representation is still the Double 123456.789

If you do it correctly, opening a WB created in SA with SA settings by someone in the US should automatically adjust the presentation of the data to US currency (others fields also)

[onmyway]

Hi Paul,

Thank you for the wonderful explanation of how the UF deals with string, and how Excel stores it as a double. I have learned something new yet again. :)

Unfortunately, it is not so simple (I wish it was!): On my side, I HAVE to make use of assigning formatting rules to specific textBoxes, otherwise users will enter absolute garbage. This will greatly effect reporting etc.

That is why I use:

If Not IsNumeric

and

TextBox1.Value = Format(TextBox1.Value, "#,##0.00")

Now, the problem with this is, that if my regional setting is on English South Africa, using the (,) as decimal, one cannot use the (.) when capturing. It sees the (.) point as non-numeric! This triggers my If Not IsNumeric.

That is why I have resorted to elaborate methods to allow the user to capture using the (.) point, as it is much simpler and quicker, being part of the num-pad. So, temporarily changing my decimal to (.), and reverting back to (,) seems to work.

i would just like to make sure that my code is OK, and that my variable method will store the user's default setting, and change it back to his default setting regardless of whether the user's setting was a (.) or (,) to start off with.

[jonh]

It's not a good idea to change users computer settings.
You can easily turn a full stop into a comma as the user is typing into the textbox(s).


Public Function ChangeFStoComma(ByRef KeyAscii As MSForms.ReturnInteger)
If usecomma Then '<-- set to true to switch fs to comma
If KeyAscii = Asc(".") Then KeyAscii = Asc(",")
End Function

e.g.


Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
ChangeFStoComma KeyAscii
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
ChangeFStoComma KeyAscii
End Sub

[onmyway]

hi jonh

I like your method, and it makes sense not to change the system settings.

I have tested this code which seems to work great:


Public Function ChangeFStoComma(ByRef KeyAscii As MSForms.ReturnInteger)
If KeyAscii = Asc(".") Then KeyAscii = Asc(",")
End Function



Private Sub txtStockSOS1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
ChangeFStoComma KeyAscii
End Sub


Tell me something; is there any easy way of applying this to multiple fields?

[jonh]

Tell me something; is there any easy way of applying this to multiple fields?

Not that I know of, sorry.

[onmyway]

thanks jonh, I really like it, and have chosen your method. :)