VBA NumberFormat problem

[Jac Tremblay]

Hi,
I use Excel 2000 french version with Windows XP Pro.
When I use this:
Selection.NumberFormat = _
"* # ##0.00\ ;[Red] * (# ##0.00);""- """
The numbers > 999 999 do not display the space (separator for the thousands
in French).
When I use this:
Selection.NumberFormat = _
"* # ### ##0.00\ ;[Red] * (# ### ##0.00);""- """
The numbers < 1 000 display a space before the opening parenthese (which is
not acceptable).
Another thing: I would like the numbers > 999 999 999,99 to display as "###
### ### ##0,00" but how many # should one use to satisfy Excel (for negative
number as well)?
Note: in French, the decimal point is a comma.
Thanks.

 

[Jim Rech]

Try this:

Selection.NumberFormatLocal = _
"* # ##0,00\ ;[Red] * (# ##0,00);""- """


--
Jim
| Hi,
| I use Excel 2000 french version with Windows XP Pro.
| When I use this:
| Selection.NumberFormat = _
| "* # ##0.00\ ;[Red] * (# ##0.00);""- """
| The numbers > 999 999 do not display the space (separator for the
thousands
| in French).
| When I use this:
| Selection.NumberFormat = _
| "* # ### ##0.00\ ;[Red] * (# ### ##0.00);""- """
| The numbers < 1 000 display a space before the opening parenthese (which
is
| not acceptable).
| Another thing: I would like the numbers > 999 999 999,99 to display as
"###
| ### ### ##0,00" but how many # should one use to satisfy Excel (for
negative
| number as well)?
| Note: in French, the decimal point is a comma.
| Thanks.
| --
| Jac Tremblay

 

[Bernard Liengme]

Jim this gives me results such as 1,2 34,567
There is a space between 2 and 3 but commas are still used for thousands
separators
There are no decimal places
best wishes

 

[Jim Rech]

This worked great for me with French settings of course. So strange it
wouldn't for you.<g>

--
Jim
| Jim this gives me results such as 1,2 34,567
| There is a space between 2 and 3 but commas are still used for thousands
| separators
| There are no decimal places
| best wishes
| --
| Bernard
| | > Try this:
| >
| > Selection.NumberFormatLocal = _
| > "* # ##0,00\ ;[Red] * (# ##0,00);""- """
| >
| >
| > --
| > Jim
| > | > | Hi,
| > | I use Excel 2000 french version with Windows XP Pro.
| > | When I use this:
| > | Selection.NumberFormat = _
| > | "* # ##0.00\ ;[Red] * (# ##0.00);""- """
| > | The numbers > 999 999 do not display the space (separator for the
| > thousands
| > | in French).
| > | When I use this:
| > | Selection.NumberFormat = _
| > | "* # ### ##0.00\ ;[Red] * (# ### ##0.00);""- """
| > | The numbers < 1 000 display a space before the opening parenthese
(which
| > is
| > | not acceptable).
| > | Another thing: I would like the numbers > 999 999 999,99 to display as
| > "###
| > | ### ### ##0,00" but how many # should one use to satisfy Excel (for
| > negative
| > | number as well)?
| > | Note: in French, the decimal point is a comma.
| > | Thanks.
| > | --
| > | Jac Tremblay
| >
|
|

 

[Jac Tremblay]

Hi Jim,
It does work perfectly. I guess the NumberFormatLocal uses the local
settings from the control panel.
I am now at home with Excel 2007 English with Windows XP Pro French. I will
try it tomorrow at the office where Excel 2000 is still in the portrait.
Thank you for your reply. It makes my day.

 

[Jac Tremblay]

Hi Bernard,
Try to change your local settings in the control panel. Make sure you have
this for the number format:
123 456 789,00
Wish you the best.
--
Jac Tremblay

Jim this gives me results such as 1,2 34,567
There is a space between 2 and 3 but commas are still used for thousands
separators
There are no decimal places
best wishes
--
Bernard

Try this:

Selection.NumberFormatLocal = _
"* # ##0,00\ ;[Red] * (# ##0,00);""- """


--
Jim
| Hi,
| I use Excel 2000 french version with Windows XP Pro.
| When I use this:
| Selection.NumberFormat = _
| "* # ##0.00\ ;[Red] * (# ##0.00);""- """
| The numbers > 999 999 do not display the space (separator for the
thousands
| in French).
| When I use this:
| Selection.NumberFormat = _
| "* # ### ##0.00\ ;[Red] * (# ### ##0.00);""- """
| The numbers < 1 000 display a space before the opening parenthese (which
is
| not acceptable).
| Another thing: I would like the numbers > 999 999 999,99 to display as
"###
| ### ### ##0,00" but how many # should one use to satisfy Excel (for
negative
| number as well)?
| Note: in French, the decimal point is a comma.
| Thanks.
| --
| Jac Tremblay