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

Topic

New Reply

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?

Reply | Quote

Replies

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

    Worksheets("Sheet1").PageSetup.CenterHeader = Format(Date, "mmmm d, yyyy")

Reply | Quote

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.

Reply | Quote

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:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

End Sub

Put the code between those two lines.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote