VBA NumberFormat problem
 

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

VBA NumberFormat problem
 

Try this:

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


--
Jim
"Jac Tremblay" wrote in message
...
| 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

VBA NumberFormat problem
 

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
"Jim Rech" wrote in message
...
Try this:

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


--
Jim
"Jac Tremblay" wrote in message
...
| 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

VBA NumberFormat problem
 

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

--
Jim
"Bernard Liengme" wrote in message
...
| 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
| "Jim Rech" wrote in message
| ...
| Try this:
|
| Selection.NumberFormatLocal = _
| "* # ##0,00\ ;[Red] * (# ##0,00);""- """
|
|
| --
| Jim
| "Jac Tremblay" wrote in message
| ...
| | 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
|
|
|

VBA NumberFormat problem
 

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


"Jim Rech" wrote:

Try this:

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


--
Jim
"Jac Tremblay" wrote in message
...
| 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

VBA NumberFormat problem
 

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


"Bernard Liengme" wrote:

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
"Jim Rech" wrote in message
...
Try this:

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


--
Jim
"Jac Tremblay" wrote in message
...
| 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