Maybe you can use a numberformat that's very close to the naked eye--but is
actually different:
Worksheets("Sheet1").Columns("C"). _
NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
becomes
Worksheets("Sheet1").Columns("C"). _
NumberFormat = " $#,##0.00_);[Red]( $#,##0.00)"
(with a couple more spaces)
And since it's different than the format that excel/windows uses to collaborate
against you, it may work!
veryeavy wrote:
Many Thanks.
Silly Me - didn't look there.
Unfortunately in our drab, bolted down, corporate world we don't seem to be
allowed to change this option ...
:
In WinXP Home, I can get to the Control panel
then Regional and language options
then click the customize button
On the currency tab, I see "negative currency format" as the 3rd dropdown.
veryeavy wrote:
Hi Dave,
I don't seem to have a specific negative currency option.
My Options are Number: and Currency:
and are set to:
123,456,789.00 and $123,456,789.00 respectively.
I am running Excel 2003 SP1 on Windows XP Professional SP2.
TIAA
Matt
:
Excel depends on somethings from windows.
Try changing the negative currency format under windows regional settings (under
control panel) to:
($1.1)
When I changed it to:
-$1.1
I had the same problem as you.
And I had to close excel and reopen to see the difference.
veryeavy wrote:
Further Developments
Irony of Ironies - this is the example in the Help system:
Example
These examples set the number format for cell A17, row one, and column C
(respectively) on Sheet1.
Worksheets("Sheet1").Range("A17").NumberFormat = "General"
Worksheets("Sheet1").Rows(1).NumberFormat = "hh:mm:ss"
Worksheets("Sheet1").Columns("C"). _
NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
and this doesn't work either!
My colleague very helpfully suggested using "Styles" and while this should
be workable this is starting to turn something that should be super-neat and
tidy into something more cumbersome (having to create the style
programmatically before applying it).
So suggestions as to why the simple approach isn't working are still sought.
TIA and Cheers, Matt
:
Hi,
Hope this qualifies as "programming".
the following macro:
Selection.NumberFormat = "#,##0_);(#,##0)"
does not do the job I was hoping.
On examination the format that is applied is:
#,##0;-#,##0
any way to get my preferred format to "take"?
TIA, Matt