Footers via VBA (XL XP)

Topic

New Reply

I am trying to set up some code that changes footers on all the tabs. My code looks like this …
I’ve replaced the [ and ] with ( and ) so that it doesn’t display the special woody items like – how do I turn the tags off?

	With ActiveSheet.PageSetup
		.LeftFooter = "&(FILE)"
		.CenterFooter = "&(TIME)"
		.RightFooter = "&[DATE]"
	End With

Then I run that on the current sheet and then go and look at the results using File, Setup and select the Header/Footer tab, it looks like this …

If I open the custom footer dialog, it looks like this …

When I close the custom footer dialog, it looks like this (file name removed) …

So, as far as I can see, the code I entered is correct but it is not ‘taking’ – any suggestions?

Reply | Quote

Replies

You can use the special “tag” tags: [t] will be turned into [ and [/t] will be turned into ]. You can type them yourself, or use TAG from the 1-Click TagPanel.

Reply | Quote

[grin] – thx Hans – works like a dream

Reply | Quote

The codes you see in the page setup dialog are different from what VBA expects:

    With ActiveSheet.PageSetup
        .LeftFooter = "&F"
        .CenterFooter = "&T"
        .RightFooter = "&D"
    End With

Reply | Quote

unbelievable! I was planning to give the user control on the set up of these footers and then copy the footers that they have set up on one tab to all tabs. However, vba returns “&[DATE]” for the date function within a footer but it needs “&D” to initialize the date function within the footer.

I guess that I will have to scan the users selected footers and replace all “&[DATE]” like items with “&D”.

Link to the MS KB on XL2000: Inserting and Formatting Text in Headers and Footers

Reply | Quote

Try

.LeftFooter = “&F”
.CenterFooter = “&T”
.RightFooter = “&D”

Reply | Quote