formatting a footer date

[Lawrence Dodge]

Hi--

Thank you very much for this great resource. Even though I don't use every point, I read them and file them all -- maybe one day I will get a chance to use them. My question is this:

I put &[date] into my footers and always get the format dd-mm-yy. I have set the control panel date to dd-MMM-yy (with MMM being the first three letters of the month). How can I format the footer? Thank you in advance

Lawrence Dodge

ldodge@westbycreamery.com

 

[Hui]

Lawrence


You don't have any option there unfortunately,

However you can use a small macro to do whatever you want


Add the following to the Workbook Object or Specific worksheet object within VBA

This macro will automatically update the footer every time you print


Change the codes to suit your needs, or delete lines as necessary

[pre]

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = Application.WorksheetFunction.Text(Date, "dd mmmm yyyy")
.CenterFooter = Application.WorksheetFunction.Text(Date, "d m y")
.RightFooter = Application.WorksheetFunction.Text(Date, "dd.mm.yy")
End With
End Sub

[/pre]

 

[Lawrence Dodge]

Thank you much, is there a way to make this a universal macro for all future workbooks to have and use?

Lawrence

 

[Hui]

You can save it in your Personal Macro Workbook


To make it always available, you can add it to a file called personal.xlsm and then link it to a Button in the Toolbar. Personal.xlsm must be in your XLSTART directory


Have a read of http://www.mvps.org/dmcritchie/excel/getstarted.htm


Just be aware that when you share workbooks macros in your Personel.xlsm/b file wont be available to those recieving the file.

 

[Lawrence Dodge]

You are wonderful thank you, now to see if I can get it to work hehehe. Have a great day.

Lawrence