Dots to comma in userform

[Ako_____]

Hi,

I have a really annoying problem which I just can't solve on my own. I hope some of you more experienced VBA programmers can help me out!

Ok, the thing is that I have a userform with some textboxes and comboboxes. Some of the boxes have predetermined values and some are left empty for the user to fill out.
In the beginning the decimalseparators were commas just as I want them to be but suddenly for a reason which is unknown to me the decimalseparators changed to dots. The result is that my code now perceives the numbers as if there are no decimal separators at all and I am getting completely wrong results.

I really don't understand what is going on... Maybe I have accidently hit a combination on the keyboard which has changed some settings...?

Does anybody know how to solve my problem?

[Aussiebear]

Have you changed the number format in some way?

[Teeroy]

If you are using Windows 7 it may not be an excel problem. Apparently Windows 7 can get its regional settings confused but it's an easy fix; change your regional settings then change them back.

I only know one way that you intentionally override the system default and it's not something you could do accidentally. Have a look at This.

[snb]

You can check the regional settings, in this case the decimalseparator, using:

[vba]
sub M_snb()
msgbox Application.International(3)
end sub
[/vba]

@Teeroy
There's a very simple oneliner to change the regional settings....

[Teeroy]

Hi @SNB, I didn't mention Application.International because as far as I was aware it's read only and @Ako wanted to know how the decimal separator could have changed. As for the one-liner, hit me with it (I've seen you do some pretty funky things with a one-liner, though I don't know that I'd call them all simple ). Some of your one-liners take some working out but I learn a thing or three in the process.

[Kenneth Hobs]

An API or registry method can be used to change the settings. In xp: Start > Settings > Control Panel > Regional and Language Options > Customize.

Of course the controls are string based so how a number is interpreted can trip you up.

[snb]

@teeroy

I thought it to be worth while to check first whether any changes in the regional settings had taken place before assuming they had.

To retrieve / chnage international settings; in this case the decimal separator:

[vba]
Sub M_snb()
with CreateObject("wscript.shell")
msgbox .regread("HKEY_CURRENT_USER\Control Panel\International\sDecimal")
msgbox .regread("HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\de cimalseparator")

' changing:

.regwrite "HKEY_CURRENT_USER\Control Panel\International\sDecimal", "|"
.regwrite "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\decimalsepa rator", "|"
end with
end sub
[/vba]

[Ako_____]

Hi,

I have tried all the solutions that you have suggested, but so far nothing works

All the checks I have made based on your good advises showed that my computer is setup to use "," as the decimalseparator and "." as the thousandseparator.

Just to make sure that it is not my code which is the problem I have tried following:

1: I have created a userform in a new document. The userform contained a textbox which I gave an initial value of 2,4.
This is still displayed as 2.4 when I am running the userform.

2: I have tried to change the national settings so that the decimalseparator is ".".
This works very well and my code are now getting the correct results.

Of course I could just continue to use "." as decimalseparator but since we in Denmark use "," I would really like my code to work for our national settings.

Do any of you have any alternative ideas?

[Kenneth Hobs]

You need to post your workbook or all the code for the userform for us to troubleshoot.

As I said, a value of "2,4." is a string and should not be changing by whatever setting you have. Now putting a textbox string value into a numeric cell type is another matter.

[snb]

You probably are using 'linkedcell'. Do not use that.