Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 "veryeavy" wrote: 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 |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 "veryeavy" wrote: 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 -- Dave Peterson |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
it works for me.
in a blank worksheet, enter a number in a1 and make sure it's selected then in the vb editor open the immediate window (view/immediate window or ctrl-G) then paste your code in there, place your cursor on the line of code and hit enter: Selection.NumberFormat = "#,##0_);(#,##0)" does it format your number correctly? -- Gary "veryeavy" wrote in message ... 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 |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
You may not have to close Excel and reopen--I may have not hit the Apply button
(oopsie!). 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 "veryeavy" wrote: 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 -- Dave Peterson |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
No.
Can I expect to get differing results via the immediate window rather than the macro? This is quite literally a one line macro so I wouldn't have expected a different result ... Was worth a try tho' :-) Thx "Gary Keramidas" wrote: it works for me. in a blank worksheet, enter a number in a1 and make sure it's selected then in the vb editor open the immediate window (view/immediate window or ctrl-G) then paste your code in there, place your cursor on the line of code and hit enter: Selection.NumberFormat = "#,##0_);(#,##0)" does it format your number correctly? -- Gary "veryeavy" wrote in message ... 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 |
#7
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 "Dave Peterson" wrote: 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 "veryeavy" wrote: 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 -- Dave Peterson |
#8
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Your code worked fine for me also.
Does this small code help in debugging. It returns the current format, what it should be, and what it was changed to. Sub Test() Const s As String = "#,##0_);(#,##0)" Debug.Print Range("A1").NumberFormat Range("A1").NumberFormat = s Debug.Print s Debug.Print Range("A1").NumberFormat End Sub For me, it returned: General #,##0_);(#,##0) #,##0_);(#,##0) -- HTH :) Dana DeLouis Windows XP & Office 2003 "veryeavy" wrote in message ... 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 |
#9
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
veryeavy -
Can I expect to get differing results via the immediate window rather than the macro? < Yes, you can expect different formatting in the VBE immediate window compared with the formatting in a cell in an Excel worksheet. - Mike http://www.mikemiddleton.com |
#10
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 "Dave Peterson" wrote: 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 "veryeavy" wrote: 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 -- Dave Peterson -- Dave Peterson |
#11
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 ... "Dave Peterson" wrote: 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 "Dave Peterson" wrote: 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 "veryeavy" wrote: 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 -- Dave Peterson -- Dave Peterson |
#12
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 ... "Dave Peterson" wrote: 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 "Dave Peterson" wrote: 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 "veryeavy" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Thanks Dana)
Now we are getting somewhere. It does indeed return the "supposed" format that I have applied. That leaves the question - Why does using this line of code and using the "format cells" dialog lead to two different results (as I am presuming this discounts Dave's helpful suggestion re Settings - thanks Dave!)? "Dana DeLouis" wrote: Your code worked fine for me also. Does this small code help in debugging. It returns the current format, what it should be, and what it was changed to. Sub Test() Const s As String = "#,##0_);(#,##0)" Debug.Print Range("A1").NumberFormat Range("A1").NumberFormat = s Debug.Print s Debug.Print Range("A1").NumberFormat End Sub For me, it returned: General #,##0_);(#,##0) #,##0_);(#,##0) -- HTH :) Dana DeLouis Windows XP & Office 2003 "veryeavy" wrote in message ... 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 |
#14
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
A very good trick it is too - thanks Dave - that works.
And yes - I do feel like I have been collaborated over by Excel/Windows - lol. However one further development is I was wrong earlier (don't ask!) and I can and have changed the negative number setting to "(1.1)" And the formatting as I really want it still displays as -1,000 via the macro and (1,000) via the Format Cells functionality. "Dave Peterson" wrote: 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 ... "Dave Peterson" wrote: 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 "Dave Peterson" wrote: 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 "veryeavy" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
It would be pretty funny if you didn't need to actually use it, huh?
(I have no idea why there's a change.) veryeavy wrote: A very good trick it is too - thanks Dave - that works. And yes - I do feel like I have been collaborated over by Excel/Windows - lol. However one further development is I was wrong earlier (don't ask!) and I can and have changed the negative number setting to "(1.1)" And the formatting as I really want it still displays as -1,000 via the macro and (1,000) via the Format Cells functionality. "Dave Peterson" wrote: 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 ... "Dave Peterson" wrote: 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 "Dave Peterson" wrote: 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 "veryeavy" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#16
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Right)
And if the format never existed and I never got "addicted" to it and I wasn't a beanie (accountant) then life would be SWEET ... Thanks for your help (and everyone else too). Cheers, Matt "Dave Peterson" wrote: It would be pretty funny if you didn't need to actually use it, huh? (I have no idea why there's a change.) veryeavy wrote: A very good trick it is too - thanks Dave - that works. And yes - I do feel like I have been collaborated over by Excel/Windows - lol. However one further development is I was wrong earlier (don't ask!) and I can and have changed the negative number setting to "(1.1)" And the formatting as I really want it still displays as -1,000 via the macro and (1,000) via the Format Cells functionality. "Dave Peterson" wrote: 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 ... "Dave Peterson" wrote: 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 "Dave Peterson" wrote: 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 "veryeavy" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
custom number formats | Excel Discussion (Misc queries) | |||
Custom Number Formats - Help | Excel Discussion (Misc queries) | |||
custom number formats | Excel Worksheet Functions | |||
[$-409] in Custom Number Formats | Charts and Charting in Excel | |||
Custom Number Formats | Excel Worksheet Functions |