PDA

View Full Version : Dots to comma in userform



Ako_____
11-04-2012, 12:28 PM
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? :help

Aussiebear
11-04-2012, 07:01 PM
Have you changed the number format in some way?

Teeroy
11-04-2012, 11:10 PM
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 (http://blogmines.com/blog/2010/03/11/how-to-change-the-decimal-separator-in-excel-2010/).

snb
11-05-2012, 03:49 AM
You can check the regional settings, in this case the decimalseparator, using:


sub M_snb()
msgbox Application.International(3)
end sub


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

Teeroy
11-05-2012, 05:08 AM
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 :biggrin: ). Some of your one-liners take some working out but I learn a thing or three in the process.

Kenneth Hobs
11-05-2012, 06:07 AM
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
11-05-2012, 07:20 AM
@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:


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\decimalsepar ator")

' changing:

.regwrite "HKEY_CURRENT_USER\Control Panel\International\sDecimal", "|"
.regwrite "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\decimalsepar ator", "|"
end with
end sub

Ako_____
11-05-2012, 11:59 AM
Hi,

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

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? :dunno

Kenneth Hobs
11-05-2012, 12:07 PM
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
11-05-2012, 01:19 PM
You probably are using 'linkedcell'. Do not use that.