Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jul 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Format in Header/Footer (2000 SR-1a)

    When I click on the date icon in Custom Header/Footer it currently inserts the date in the format mm/dd/yyyy which I think must be coming from the Regional Settings Short Date Format.

    How do I insert the date in the format: July 31, 2001?

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

    Re: Date Format in Header/Footer (2000 SR-1a)

    Put the following line of VBA code into the Workbook BeforePrint event routine.

    <pre> Worksheets("Sheet1").PageSetup.CenterHeader = Format(Date, "mmmm d, yyyy")
    </pre>

    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jul 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Format in Header/Footer (2000 SR-1a)

    Thanks. If it does not require too much detail, can you give me additional information on how to put this VBA code into the workbook. I'm not familiar with how the programming works. I've only recorded macros thus far.

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

    Re: Date Format in Header/Footer (2000 SR-1a)

    Here is the procedure:

    1- Open the workbook.

    2- Press Alt+F11 - This should open the VB Editor.

    3- The project explorer window should be on the left side of the screen (It should say Project - VBAProject in the title bar). In the project tree, find the line that says VBAProject with your file name in parenthesis. If there is a plus sign to the left of this line, click on the plus sign which should expand the project.

    4- Find the line under your workbook project that says "Microsoft Excel Objects." If it has a plus sign to its left, click on the plus sign to expand the objects.

    5- Under "Microsoft Excel Objects" double click on ThisWorkbook. This should open a code edit window on the right side of the screen.

    6- At the top of the code edit window should be two combo boxes. The left combo box should have (General) in it. Click on the arrow and select Workbook from the list. This should show an empty procedure for the Workbook_Open event in the lower part of the screen.

    7- Click on the arrow in the right combo box to display a list of event routines. Scroll up if necessary to find BeforePrint and select it. You should now see an empty BeforePrint routine that look like this:

    <pre>Private Sub Workbook_BeforePrint(Cancel As Boolean)

    End Sub
    </pre>


    Put the code between those two lines.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Jul 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Format in Header/Footer (2000 SR-1a)

    Thanks!! My first venture into VB programming and it worked. I've been there before, but never on purpose. Thanks again, it worked great.

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Format in Header/Footer (2000 SR-1a)

    Legare,

    Can this be expanded to encompass all existing (and / or new) Excel documents easily?

    Also, could this be setup as a button on a toolbar to apply it "as required". My VB knowledge is very, very, sparse. Any help you can offer would be a great help.
    Christopher Baldrey

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

    Re: Date Format in Header/Footer (2000 SR-1a)

    This could easily be made to apply to all new workbooks, or all new workbooks created from a specific template. Just create an empty workbook and put the code into the before print event routine as shown. If you want it to apply to all new workbooks, then do a File SaveAs and save the workbook as a template named Book.xlt in the XLStart directory. If you want it to apply only to workbooks created from a specific template, then do the File SaveAs and save it as a template with a name you choose and put it in your template directory.

    I do not know of an easy way to have it apply to existing workbooks.

    You could easily put that line of code in a general macro and assign that macro to a button. This would put the date into the Header/Footer when the button was pressed, but it would not be automatically updated when the sheet was printed.
    Legare Coleman

Posting Permissions

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