Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Number formatting (2003)

    Can anyone explain how to set the default number format (for a negative number) to return the number in parenthesis?
    I have the same version of Excel 2003 Pro at home and at work, and at work the default is (1,234) and at home the default is -1,234.

    I know how to manually make the formatting change, but would like a tip on how to set the default at home to (1,234).

    Thanks for any advice you can provide
    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number formatting (2003)

    I think Excel takes the default from the Regional Settings control panel in Windows. You can specify there how negative amounts are formatted.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Number formatting (2003)

    If you have a book.xlt template file in your startup directory, you can alter the Normal style in that to use a custom number format, but that will only work for new files. You could also write a simple macro and assign it to a menu button to use on existing worksheets.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Number formatting (2003)

    Hans, Thanks, I tried your suggestion but was not successful, regional settings has an option for ( ), but excel didn't respond to the change even after rebooting. Any other ideas?

    Thanks for your help!
    Tom

  5. #5
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Number formatting (2003)

    Can you show me the simplest macro to use? I am not well versed creating macros, but I do know how to use them. [img]/forums/images/smilies/smile.gif[/img]

    Thanks
    Tom

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number formatting (2003)

    You can create a macro in your Personal.xls to format numbers the way you want, and assign is to a custom toolbar button.
    See <!post=Legare Coleman's Personal.xls Tutorial (All),118382>Legare Coleman's Personal.xls Tutorial (All)<!/post> if you need to know how to create and use Personal.xls.

    The macro could look like this for "general" number format:
    <code>
    Sub CustomGeneral()
    On Error Resume Next
    Selection.NumberFormat = "General;(General)"
    End Sub
    </code>
    Or for "fixed" number format:
    <code>
    Sub CustomNumber()
    On Error Resume Next
    Selection.NumberFormat = "#,##0_);(#,##0)"
    End Sub
    </code>
    After using the latter macro, you can use the Increase Decimal and Decrease Decimal buttons on the Formatting toolbar to display more or fewer decimals.

    To assign a macro to a toolbar button:
    - Select Tools | Customize...
    - Activate the Commands tab.
    - Select Macros in the list of Categories.
    - Select Custom Button in the list of Commands.
    - Drag it to the Formatting toolbar and drop it in a convenient location.
    - Right-click the new button to assign the macro, change the caption, etc.

  7. #7
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Number formatting (2003)

    Thanks again Hans, the macro worked perfectly.
    Can you recommend a free resource for me to use to learn more about visual basic?
    Thanks
    Tom

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number formatting (2003)

    A good start is to record a macro, then study the generated code. See Creating a macro with no programming experience using the recorder (the article is for Word, but it works the same in Excel).

    MSKB article How to use Visual Basic for Applications in Excel contains some useful links.

    And reading the questions and answers in this and similar forums can be very useful.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Number formatting (2003)

    <post:=320,321>post 320,321</post:> has some other links...

    Steve

  10. #10
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Number formatting (2003)

    Thanks for all the help and links!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •