Attn Gord Dibben
In a thread from a year ago at
http://www.officekb.com/Uwe/Forum.as...er-Date-Format I'm interested in your post dated 30 Jan 2009 00:26 GMT. I'm having a similar problem with Excel 2007 and have seen your name replying to similar threads all over the place. First, I'll 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 "Printed 9 Jan 2010"? How do I fix it? I haven't been able to find anyplace to set it. In Excel Options, I've gone through everything several times and don't 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's honoring the computer long date but not the computer short date. Though I'm pretty decent in QuattroPro, I'm 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 didn't 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 won't 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. |
Attn Gord Dibben
Hey Kathy
Thisworkbook will come up if you hit Alt+F11. look on the left side of the window that opens.. "KathrynBassett" wrote: In a thread from a year ago at http://www.officekb.com/Uwe/Forum.as...er-Date-Format I'm interested in your post dated 30 Jan 2009 00:26 GMT. I'm having a similar problem with Excel 2007 and have seen your name replying to similar threads all over the place. First, I'll 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 "Printed 9 Jan 2010"? How do I fix it? I haven't been able to find anyplace to set it. In Excel Options, I've gone through everything several times and don't 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's honoring the computer long date but not the computer short date. Though I'm pretty decent in QuattroPro, I'm 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 didn't 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 won't 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. . |
Attn Gord Dibben
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 On Fri, 15 Jan 2010 01:51:23 GMT, "KathrynBassett" <u57499@uwe wrote: In a thread from a year ago at http://www.officekb.com/Uwe/Forum.as...er-Date-Format I'm interested in your post dated 30 Jan 2009 00:26 GMT. I'm having a similar problem with Excel 2007 and have seen your name replying to similar threads all over the place. First, I'll 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 "Printed 9 Jan 2010"? How do I fix it? I haven't been able to find anyplace to set it. In Excel Options, I've gone through everything several times and don't 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's honoring the computer long date but not the computer short date. Though I'm pretty decent in QuattroPro, I'm 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 didn't 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 won't 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. |
All times are GMT +1. The time now is 04:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com