Page Header/Footer - Take info from cell

[ajm5807]

Any help please

I want to create a header/footer for a sheet taken from the info in a particular cell.

Its straight forward enough to create it from filename/tab name etc but cant see how I can take it from a cell

Any thoughts

Thanks

[6StringJazzer]

There is no built-in way to do this. It requires a macro. Here is code that will set the center footer of whatever sheet is active to the contents of cell A1 on that sheet. This can also be enhanced to update it automatically any time that the contents of A1 changes.

Please Login or Register  to view this content.

Edit: Removed unnecessary With/End With although it would have worked fine

[ajm5807]

That's worked a treat, thank you

Can it be formatted in a preset style, IE bold, particular font, font size etc, or is that asking too much?

[6StringJazzer]

It can be set to do all those things; there are special codes that can be inserted into the footer text to make it bold, font, size, etc. However, I don't know of a reference that lists them all. What I do, even though I'm fairly experienced with VBA, is to record a macro to set up the footer then look at the generated code to see what it did. Do be wary of macro bloat.

[ajm5807]

The macro is set up to add the Footer from a cell but I would then have to go in and manually change the font/size/bold etc...can these be set in the same macro, the one you suggested?

[ajm5807]

Sorry, read your reply again...you're suggesting I record a macro, changing the Footers appearance to what I want, then copying the result into the original macro???

[6StringJazzer]

Almost. I'm suggesting you record a macro, changing the footer's appearance to what you want, then study the resulting macro to learn how it works, then modify the macro I provided. For example, this modifies my macro to use Algerian font, bold, 14-point size.

Please Login or Register  to view this content.

[ajm5807]

OK, would this embed the font I choose or would someone see a different font if I chose something unusual?

[6StringJazzer]

The font code is just a text string that refers to that font by name. If that font is not loaded on the machine, then Excel or Windows will decide how to display it. I do not know how it handles that, possibly uses a default.

[ajm5807]

Thanks very much for the help Jeff