Force Format decimal to (.) regardless of regional settings

Hi all,

i hope you can help.

Due to different regional settings used, and especially the decimal (,) or (.), I have been making use of the code below to allow for the capture of (.) when the regional decimal is set to (,).

But this obviously only allows me to capture using the (.), but then changes the (.) back to (,) based on the regional decimal settings.

How can i force format my values captured throughout my UserForm to be (.)? I.e., when saving to excel, may decimal in my values will be (.), regardless of my regional settings.

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

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

Thank you!

This should solve your problem. Put in workbook open.

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

Hi Jasper,

Thank you for your response!

It seems like a simple solution, but it does not seem to work.

I put the code as follows:

Private Sub Workbook_Open()
    Application.DecimalSeparator = "."
    Application.ThousandsSeparator = ","
    Application.UseSystemSeparators = False
End Sub

But when I run a procedure as below (bottom), it triggers my error:

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

I.e., it does not see it as a numeric value.

But, if my systems setting is set to use (.), it does not give this error. Also, if I use the method I used in my first post, I do get to put in either (.) or (,), but the decimal displays as the system decimal (,) and not (.) as per the Workbook_Open event.

Private Sub txtHourMetersSOS1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

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

        MsgBox "Sorry, only numbers allowed"
       
        txtHourMetersSOS1.Value = vbNullString
        
        Cancel = True
        
       Else
        
        If Trim(Me.txtHourMetersSOS1.Value) = "" Then
        
        MsgBox "Please enter a value"
    
        Cancel = True
    
       Else
       
            txtHourMetersSOS1.Value = Format(txtHourMetersSOS1.Value, "#,##0.00")
        
        End If
    
    End If

End Sub

Any ideas?

Yes, i did close and re-open a few time.

I tried the code in my txtHourMetersSOS1_Enter event as well - does not work. Weird....

i am not too sure I know what you mean with this:

What dimension did you set for "txtHourMetersSOS1" ?

All my dimensions are in this:

Private Sub txtHourMetersSOS1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

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

        MsgBox "Sorry, only numbers allowed"
       
        txtHourMetersSOS1.Value = vbNullString
        
        Cancel = True
        
       Else
        
        If Trim(Me.txtHourMetersSOS1.Value) = "" Then
        
        MsgBox "Please enter a value"
    
        Cancel = True
    
       Else
       
            txtHourMetersSOS1.Value = Format(txtHourMetersSOS1.Value, "#,##0.00")
        
        End If
    
    End If

End Sub

I also tried this suggestion:

 If Not IsNumeric(txtHourMetersSOS1/1) And txtHourMetersSOS1.Value <> vbNullString Then

But got an error on the part "/ 1" that replaced the .Value.

Ohhh!

My txtHourMeterSOS is a simple textBox. The code used, is simply to format it in my desired format when one exits the field.

Hi Jasper

Will see what I can do tomorrow. I have had it for today. Thanks for all your help!

Cheers