Formatting Footers

  • Thread starter Thread starter Stuart Grant
  • Start date Start date
S

Stuart Grant

I want to use the date in a footer like 13 Feb 06 which is the short date
format in my Windows Regional Settings. If I use &D it comes out 13 02
2006. I don't see how to use the d mmm yy format string.
Stuart
 
You'll have to use some VBA to to this.

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Me.Worksheets("Sheet1").PageSetup
.LeftFooter = Format(Date, "d mmm yyyy")
End With
End Sub

If you want to try...

rightclick on the excel icon to the left of the File|Edit|View toolbar.
Select view code and paste that subroutine into the code window.

Change the sheet name that you need (and .leftfooter to what you need) and back
to excel to do File|Print preview.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you want to read more about these kinds of events:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm
 
Dave
Thank you for jumping in promptly. Actually I had worked out that this was
the way to do it and of course it works. I am moderately familiar with VBA
and for this print out want to do most of the setup with VBA. I have a
problem with the header however. I want to set it Bold and with 14 Point.
The font - Arial is OK. I can't seem to get the code right.
PageStup.CenterHeader.Font.Bold and/or .CenterHeader.Font size gives a
Syntax Error. Is there somethingg obvious that I am missing ?
Stuart
 
Record a macro when you do it manually and you'll see something like:

With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""&22 13 Feb 06"

So that could become:

With Me.Worksheets("sheet1").PageSetup
.CenterHeader = "&""Arial,Bold""&22 " & Format(Date, "dd mmm yyyy")
End With

(Bold, 22 point)

Since you're formatting the date to start with a number, it's a good idea to
leave a space between the font size and the next character.

You don't want the font size to be 2213 points!
 
And change that 22 to 14.

Stuart said:
Dave
Thank you for jumping in promptly. Actually I had worked out that this was
the way to do it and of course it works. I am moderately familiar with VBA
and for this print out want to do most of the setup with VBA. I have a
problem with the header however. I want to set it Bold and with 14 Point.
The font - Arial is OK. I can't seem to get the code right.
PageStup.CenterHeader.Font.Bold and/or .CenterHeader.Font size gives a
Syntax Error. Is there somethingg obvious that I am missing ?
Stuart
Click to expand...
 
Thanks again. This works of course. I had tried to follow the guidance in
VBA Help but found it a bit sparse and got tied up with the sequence of
quotation marks, ampersands, commas and spaces.

I have another problem with syntax in assigning a macro to a menu bar option
but don't want to keep pestering you so will post it to the group, although
would be delighted of course if you can guide me.
Stuart
 
Back
Top