Excel - Attn Gord Dibben

Asked By KathrynBassett

14-Jan-10 08:51 PM

In a thread from a year ago at
http://www.officekb.com/Uwe/Forum.aspx/excel/34822/Excel-2000-Header-Date-Format
I am interested in your post dated 30 Jan 2009 00:26 GMT. I am having a similar
problem with Excel 2007 and have seen your name replying to similar threads
all over the place. First, I will post my problem (from a copy/paste elsewhere),
then ask about something you said in that particular reply.

Vista Business SP2 in case it makes a difference to my question.
Default Dates not honoring Regional/Language Options

I have Regional/Language Options Short Date set to "dd MMM yyyy" (9 Jan 2010)
and Long Date set to "dddd, MMMM dd, yyyy" (Saturday, January 09, 2010).

In the header of my spreadsheet, I have "Printed &[Date]".

My question is, why does the header print "Printed 09 01 2010" instead of

I have not been able to find anyplace to set it. In Excel Options, I have gone
through everything several times and do not see where to change it so that it
uses the computer's settings. There must be someplace, because when
formatting a cell to use short date, and type "26 jan 10" it comes out "26 01
2010" and if I format a cell to use long date and type "26 jan 10", it comes
out correctly as "Tuesday, January 26, 2010". So it is honoring the computer
long date but not the computer short date.

Though I am pretty decent in QuattroPro, I am a novice at Excel. So I need step-
by-step instructions on fixing this.

Now, on to your post - You said: "Place this code in Thisworkbook module".
Can you explain what that means? I looked up Thisworkbook in the help file
and it did not come up with anything.

Changing what you posted in the other thread, I will modify the ActiveSheet
line to reflect the left footer that I want, and the format of the date for
same reason, resulting in below. Any reason that will not work? If it does, then
I just need to know about that Thisworkbook thing to know what to do with it.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName _
& " " & ActiveSheet.Name _
& "   " & Format(Date, "dd mmm yyyy")
End Sub

Once this is all working, do I assume correctly that I need to add this to
each workbook that I want to print with this date format?

Also, what do I do to get a date CELL to print a date like 14 Jan 2010.

15-Jan-10 03:24 AM

Hey Kathy


Thisworkbook will come up if you hit Alt+F11. look on the left side of the
window that opens..

15-Jan-10 12:41 PM

Excel respects the short date format only to a point.

dd mmm yy will not be respected.

You cannot change this within Excel except by formatting.

The BeforePrint code goes into Thisworkbook module.

Alt + F11 to open VBE, Ctrl + r to open Project Explorer.

Selecy your workbook/project and expand.

Expand Microsoft Excel Objects.  Double-click to open Thisworkbook module.

Paste the code in there.

Yes.......using this method, you must place the code in every workbook.

You could set it up differently

Add this macro to a General module in your Personal.xlsb

Sub add_date()
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName _
& " " & ActiveSheet.Name _
& "   " & Format(Date, "dd mmm yyyy")
End Sub

Run it whenever you want to add a footer to a sheet in any workbook.

To format  09 01 2010  to  9 Jan 2010  use a custom format of  d mmm yy

For more on Personal.xlsb  see Ron de Bruin's site.

http://www.rondebruin.nl/personal.htm


Gord