Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change font and/or font size in headers, footers and body via VBA
Greetings:
Having major formatting problems when trying to run my Windows based W/B's on a Mac. It appears that the only way I can resolve the problems is to change the font sizes (and likely also the column width settings) on a per SHEET basis. To do this effectively, I'm hoping there is a way that code could be used to "read" existing font size, then decrease it by ONE point. Once actual font size required is determined, the code would be adjusted to do its required changes in one run thru. Require (adjustable) changes to: Header, Footer and (active) Body of worksheet. In order to (hopefully) help expedite this problem, I'm changing all my windows based masters to use only the Arial font group. Any assistance in this matter would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change font and/or font size in headers, footers and body via VBA
The body is fairly simple
ftsize=Activecell.font.size will give you the font size of a given cell A loop like For Each Sh In Workbooks(TgtWorkbookName).Worksheets For Each Cell In Sh.UsedRange.Cells Cell.font.size=cell.font.size-1 Next Cell Next Sh Will reduce the font size in every cell by 1 The headers and footers are another story I am not sure if you can directly set the size ActiveSheet.CenterHeader.Font.Size = 16 and ActiveSheet.PageSetup.CenterHeader.Font.Size = 16 Both error out. The font size appears to be buried in the header aa=Activesheet.PageSetup.CenterHeader Yields something like "&16Page &P" with the &16 experession representing the font size. To change all of these you would have to pull in the Right, Center and Left Headers and Footers, use some thing to isolate the 16 (to make this even more difficult it could be a single digit like &8), subtract 1 from it and then replace the 16 with the resulting 15 in the string and send it back to the sheet ActiveSheet.PageSetup.CenterHeader="newstring" if they are all the same size it is a little easier, in that you could pull one sample and not repeat the subtraction. But you still have to replace every section. Sounds like a major pain, hopefully someone else has a better idea. -- If this helps, please remember to click yes. "BEEJAY" wrote: Greetings: Having major formatting problems when trying to run my Windows based W/B's on a Mac. It appears that the only way I can resolve the problems is to change the font sizes (and likely also the column width settings) on a per SHEET basis. To do this effectively, I'm hoping there is a way that code could be used to "read" existing font size, then decrease it by ONE point. Once actual font size required is determined, the code would be adjusted to do its required changes in one run thru. Require (adjustable) changes to: Header, Footer and (active) Body of worksheet. In order to (hopefully) help expedite this problem, I'm changing all my windows based masters to use only the Arial font group. Any assistance in this matter would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change colour of font in xls headers/footers | Excel Discussion (Misc queries) | |||
Excel-Default Font for Headers and Footers | Excel Discussion (Misc queries) | |||
How do I set the default font for headers and footers in Excel? | Excel Worksheet Functions | |||
Font Colors with headers and Footers | Excel Discussion (Misc queries) | |||
Headers/Footers Default Font? | Excel Discussion (Misc queries) |