PDA

View Full Version : Change Font and Size in Excel Footer



Dee Bee
06-20-2013, 11:16 AM
As a complete VBA dunce I hope you will forgive me. I am using the following VBA code in an Excel 2003 worksheet:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = FullName & Space(10) & "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time

End Sub


It works fine after a bit of a struggle but I would like to change the font size to, say, Arial 9 Point.

Would some kind soul please rewrite my code to include this.

Many thanks in advance.

Dee Bee

mancubus
06-20-2013, 02:21 PM
welcome to the forum.

office2007+:
http://msdn.microsoft.com/en-us/library/office/bb225426(v=office.12).aspx

office2003:
http://msdn.microsoft.com/en-us/library/office/bb225426(v=office.10).aspx

for your case, it will be:
ActiveSheet.PageSetup.LeftFooter = "&9&""Arial""&Z&F" & Space(10) & "Last saved: &D &T"

or
ActiveSheet.PageSetup.LeftFooter = "&9&""Arial""&Z&F Last saved: &D &T"


&9 = font size
&""Ariel"" = font name (double the double quotes)
&Z = path
&F = filename
&D = current date in international settings format
&T = current time

Dee Bee
06-21-2013, 02:17 AM
Many thanks for quick response. The code you suggested changed the font and size fine but printed the current date and time rather than the date and time the worksheet was saved as I wanted to be able to track back to see when a worksheet had been modified. With a bit of trial and error I modified the code to:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = "&""Arial,Regular""&12 &z&f" & Space(10) & "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time
End With

End Sub

This now woks fine and prints the full path and file name and also the date saved.

Must get to grips with VBA - when I have the time!

Best wishes.

mancubus
06-21-2013, 03:19 AM
you're welcome.

another way to get the last save date and time is:

ThisWorkbook.BuiltinDocumentProperties("Last Save Time")

PS:
please remember surrounding your code with VBA tags for future posts.

after copying/writing your code select all lines and hit the green VBA button on top of the quick reply panel.