PDA

View Full Version : Formatting headers & footers in excel



cookieboy66
11-05-2007, 11:18 PM
I have recorded a macro that inserts a standard header and footer onto a spreadsheet at the click of a button. Part of the footer is inserting the date and setting the font to 8 point, however I have set it up to insert a volatile date rather than today(). When I try to use TODAY() it resets the font to 10 point. Can anyone help me with this? The code is below.


Sub Header_footer()
'
' Adds a standard header and footer to a sheet
' Keyboard Shortcut: Ctrl+Shift+H
'
Application.ScreenUpdating = False
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.RightHeader = "&""Arial,Bold Italic""Company name"
.LeftFooter = "&8&Z&F" & Chr(10) & "&A"
.CenterFooter = "&8Page &P of &N"
.RightFooter = "&8 &d"
End With
Application.ScreenUpdating = True
End Sub


:beerchug:

Charlize
11-06-2007, 04:58 AM
?Sub Header_footer()
'
' Adds a standard header and footer to a sheet
' Keyboard Shortcut: Ctrl+Shift+H
'
Dim vday As String, vmonth As String, vyear As String
'why the space ? because &06 means fontsize 6 in this context
vday = Space(1) & Format(Now(), "dd")
vmonth = Format(Now(), "mm")
vyear = Format(Now(), "yyyy")
Application.ScreenUpdating = False
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.RightHeader = "&""Arial,Bold Italic""&08Company name"
.LeftFooter = "&08&Z&F" & Chr(10) & "&A"
.CenterFooter = "&08 Page &P of &N"
.RightFooter = ""
.RightFooter = "&""Arial,Bold Italic""&08&" & vday & "/" & vmonth & "/" & vyear
End With
Application.ScreenUpdating = True
End Sub

rory
11-06-2007, 06:42 AM
Do you mean:
.RightFooter = "&8 " & Date
?

Norie
11-06-2007, 07:47 AM
Cookieboy

What do you mean by a 'volatile' date?

What date do you actually want in the header?

PS Where exactly are you using TODAY in the code, I can't seem to see it anyhere.:huh:

Charlize

Isn't a bit much seperating the day/month/year out like that?:)

Charlize
11-06-2007, 08:33 AM
Charlize

Isn't a bit much seperating the day/month/year out like that?:)It depends. When you just say date for the rightfooter the first number off the date (day) isn't displayed. I think it's because the footer formatting uses
&nn for the font formatting. So &date would be &06 for font and then /11/2007. Meaning the fontsize changed to 6

That's what I think at least.

Norie
11-06-2007, 08:40 AM
Charlize

That's not quite what I mean.

You are using the Format function to get the day, year and month.

So why not use it to do it in one go.

vdate = Format(Now(), " dd/mm/yyyy")

rory
11-06-2007, 09:11 AM
Charlize,
If you add a space in between the font size and the date, then you get the full date, so use:


.RightFooter = "&8 " & Date

rather than:


.RightFooter = "&8" & Date