How to change international decimal separator
 

Hello

I can see the international setting for decimal separator with:
Application.International(xlDecimalSeparator)

but how do I change the international settings from VBA?


Finn

How to change international decimal separator
 

That depends on your version. XP and probably 2003: Record a macro while changing it in
the Tools Options International menu for proper syntax. Earlier versions follow the
WindowsControl panel's settings. Far more complex to program -and you shouldn't, it's a
global setting for the computer and/or for the user.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"Finn Petersen" wrote in message
...
Hello

I can see the international setting for decimal separator with:
Application.International(xlDecimalSeparator)

but how do I change the international settings from VBA?


Finn

How to change international decimal separator
 

Hi Finn,

I can see the international setting for decimal separator with:
Application.International(xlDecimalSeparator)

but how do I change the international settings from VBA?

Just to add to Harald's answer - Why do you think you need to?

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

How to change international decimal separator
 

Hi Stephen
Maybe I am wrong.

I write a date in a textbox in a userform.
Eks: I want to write 1st august 2003. I write in the textbox 01-08-2003(this
is the format I want to use, and which is common used in Denmark). But one
computer understands it as 8. january 2003

Isnt this due to the international date format?

Finn

"Stephen Bullen" skrev i en meddelelse
...
Hi Finn,

I can see the international setting for decimal separator with:
Application.International(xlDecimalSeparator)

but how do I change the international settings from VBA?

Just to add to Harald's answer - Why do you think you need to?

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

How to change international decimal separator
 

Hi Finn

It's a question of how the date format is set in the control panel. If it's
dd-mm-yy then we assume that this is how the user enters and uses dates. If
it's mm-dd-yy then that's what we assume.

The trick is that a date is a date is a date, how it's entered and shown is
just a question of formatting. No matter how it's entered, we assume that
the control panel and the user agree. For this Datevalue is a great tool, it
handles all common date entries (1.8 , aug 1 03, 1/8-03, almost anything)
and harmonizes it with the control panel settings. Try something like

Private Sub CommandButton1_Click()
Dim D As Date
If IsDate(DateValue(TextBox1.Text)) Then
D = DateValue(TextBox1.Text)
MsgBox "You wrote " & _
Format(D, "dddd mmmm d. yyyy")
Else
TextBox1.Text = ""
MsgBox "No date"
End If
End Sub

and do NOT prompt for something like "enter dates in format dd-mm-yyyy" -let
Datevalue do her job. The error you mention is because the user is on
dd-mm-yy and the control panel setting is mm-dd-yy. Change the computer to
match the user.
--
HTH. Best wishes Harald
Followup to newsgroup only please

"Finn Petersen" skrev i melding
...
Hi Stephen
Maybe I am wrong.

I write a date in a textbox in a userform.
Eks: I want to write 1st august 2003. I write in the textbox
01-08-2003(this
is the format I want to use, and which is common used in Denmark). But one
computer understands it as 8. january 2003

Isnt this due to the international date format?

Finn

How to change international decimal separator
 

Hi Finn,

I write a date in a textbox in a userform.
Eks: I want to write 1st august 2003. I write in the textbox 01-08-2003(this
is the format I want to use, and which is common used in Denmark). But one
computer understands it as 8. january 2003

Isnt this due to the international date format?

It's partly due to the international date format, but more to do with the way
your code is handling the text entered. As Harald pointed out, instead of
forcing specific formats on your users, it is better practice to respond to
their choice of format, as set in Control Panel, by explicitly converting
between a date number and the textual representation of it.

So given a date variable dtDateVar and a text box on a form tbDateBox, the
following VBA can be used to convert between them:

'Display a date according to the user's settings
tbDateBox.Text = Format$(dtDateVar, "Short Date")

'Interpret a date according to the user's settings
If IsDate(tbDateBox.Text) Then
dtDateVar = CDate(tbDateBox.Text)
End If


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk