Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 15th 10, 01:51 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 1
Default 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.


  #2   Report Post  
Old January 15th 10, 08:24 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 29
Default 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.

.

  #3   Report Post  
Old January 15th 10, 05:41 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,908
Default 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" <[email protected] 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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
go to a particular sheet Gord Dibben Bill Kuunders Excel Worksheet Functions 10 November 16th 09 07:16 PM
Navigation Toolbar help- Gord Dibben Martin @nohere.net Excel Discussion (Misc queries) 9 January 14th 09 10:29 AM
Filter or sumproduct (Question for Gord Dibben) capt Excel Discussion (Misc queries) 4 December 10th 07 03:33 PM
Question for Gord D DaveM Excel Discussion (Misc queries) 7 November 17th 07 06:27 PM
A question for Gord Dibben Oliver Ferns via OfficeKB.com Excel Discussion (Misc queries) 1 March 4th 05 05:12 PM


All times are GMT +1. The time now is 12:29 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017