Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA to adjust size of existing footers


I am using Excel 2007 and have multiple, similar files that already have left, right, and center footers in them.  Each file has slightly different verbiage in the left and right footers, as does each tab within each file.  Over time, people have changed the font size on some of the footers.  I need to standardize the size of all the footers without changing the existing wording.  Is this possible to do with VBA?

Thanks for any assistance you can offer with this task.




Your question can be answered with any value on a scale from "Easy" to "Impossible". Here is the problem.

  1. Excel stores the footer texts as just plain text, for example "Left". The text will be displayed and printed in the default font.
  2. When the text is supposed to be displayed in another font the instruction is added to the text itself., like "&"Aharoni,Bold"&12Left". The font definition is preceding the text. One might be tempted to split the string on the font size to retrieve the original text, but if the footer text starts with a number Excel isn't consistent, it inserts an extra space: "&"Aharoni,Bold"&12 11Left"
  3. And then there is the chance that different parts of the footer might be formatted with different fonts, like &"Aharoni,Bold"&12Left &"Aharoni,Underlined"&14 11Right left"

Therefore the verdict is that all depends upon whether you know what's in the footers. To get at the problem is to extract the text from the footers and either delete all font formatting or add the desired font formatting. If your footers have standard items, like worksheet name, date, page number etc. (incl. cell references) that wouldn't be too hard to do. But ...


Answer the Question

You must create an account to use the forum. Create an Account or Login