Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Flower Mound, Texas, USA
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Set a Default Date Format (Excel 2000 SR-1)

    Is it possible to set as a default for all new workbooks a specific date format?

    We'd like to be able to create a new notebook, press Ctrl+; and have the date appear in this format: June 2003

    When we format cells as dates, we always have to scroll down to this custom format. Can it be designated to be the default?

    Thanks, Diane

  2. #2
    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: Set a Default Date Format (Excel 2000 SR-1)

    I don't think it is possible to set the default.

    You could create a custom routine to add the date and format it.
    <pre>Sub SetDateFormat()
    ActiveCell.Value = Date
    ActiveCell.NumberFormat = "mmmm yyyy"
    End Sub</pre>


    Put it into a module in personal.xls and assign it a shortcut.(tools - macro - options...) You could use ctrl-d if you don't use the ctrl-d to delete. Ctrl-d is as simple as hitting ctrl-;

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Flower Mound, Texas, USA
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set a Default Date Format (Excel 2000 SR-1)

    Hi, Steve ... thanks for the suggestion.

    I have never worked with macros, but somehow have managed to create a module containing the routine you provided. It is currently listed under VBA Project (Book 1). I don't know how to put it into personal.xls. However, when I looked for it in Excel (tools - macro - macros), it was there. In Options, I gave it the shortcut of ctrl-d.

    I can now use this to format the cells either before or after entering data. Is this correct, or is there more to it?

    Now ... how do I copy this macro to a disk, and how do I load it into another person's computer (in the correct location, and what/where is that location)? ... Or, do I have to repeat this procedure at her computer?

    Diane

  4. #4
    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: Set a Default Date Format (Excel 2000 SR-1)

    The macro does 2 things:
    Enters the current date into the activecell
    formats the activecell to that date format

    If you run this AFTER a date is entered, the macro will enter a new date (TODAY) and format. If you want to just format, get rid of line:
    ActiveCell.Value = Date

    The macro will be available in the workbook where you added the module.
    <post#=118382>post 118382</post#> has a tutorial on the personal.xls.

    For others to use, just have them enter the code into a module.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Flower Mound, Texas, USA
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set a Default Date Format (Excel 2000 SR-1)

    Hi, Steve ... thanks for the explanations and link. When I clicked on the link a message appeared: Post Invalid. Is there another way I can access this post?

    Would you please look over the steps I'll be sending to the person who needs this information, and let me know if they are correct or if there was a better way to accomplish it?

    Create Macro
    1 - Tools | Macro | Visual Basic Editor
    2 - At "Project," select: VBA Project (Book 1)
    3 - From the Menu Bar, click: Insert | Module
    4 - Type or paste the text you provided to me (Sub ... End Sub)
    5 - File | Export File
    Save in: Office | Macros | Module1.bas | Save (I changed the name to SetDateFormat.bas)
    6 - File | Close & Return to Microsoft Excel

    Assign Shortcut
    1 - Tools | Macro | Macros
    2 - Highlight: SetDateFormat
    3 - Click: Options
    4 - Set shortcut key | OK

    Thanks a bunch for your help. The other person will really appreciate it as well as myself!

    Diane

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Personal.xls Tutorial

    Because of the problems the lounge is experiencing, many old post are currently not available. Below is what was in the post that Steve pointed you to.

    Personal.xls Tutorial

    Excel provides a special workbook called Personal.xls where you can put any macros and User Defined Functions that you want to be available for all of your workbooks to use. This workbook is hidden, so that you would not normally see it in the list of open workbooks when you click on the Window menu command. When you install Excel, this workbook is not created by the installation process, so this tutorial is to explain how to get Excel to create a Personal.xls for you, how to put your macros and User Defined Functions into it, and how to use those macros and User Defined Functions.

    Personal.xls must be created in the correct directory or it will not work. It also should have some special attributes like being hidden. The best way to create your Personal.xls is to get Excel to create it for you. You do this by recording a macro and telling Excel to put it into Personal.xls. When you do this, Excel will automatically create Personal.xls if it does not already exist, put it in the correct place, and give it all of the correct attributes. You can use the following procedure to create your Personal.xls workbook:

    1- Start Excel with a new empty workbook.
    2- Click on the Tools menu.
    3- Click on the Macro command so that the submenu flies out.
    4- In the submenu click on
    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Flower Mound, Texas, USA
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Personal.xls Tutorial

    Hi, Legare!

    Thank you so much for sending me that post. It is very helpful, and one I will use.

    Diane

Posting Permissions

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