scaling font size in headers and footers

SunD3 · Oct 30, 2008

I have excel documents with the scaling setup for each tab ranging from 60 to 100 percent (to fit the data within), and thus the headers and footers print quite small and barely readable. I was given the task to adjust the font size in the headers and footers for each page so that each header/footer looked roughly the same font size. Given the size of the document, I did not want to do this manually. As a novice code writer, I built the following code, which worked perfectly until I entered the text “2 August 2008” as LH1Text. (To test the code, I created a file with 60 tabs, adjusted the scaling from 40 percent to 100 percent for each tab, ran the code, and printed the file. Headers and footers look roughly the same size regardless of the scaled size.) With “2 August 2008” entered as LH1Text and the scaling at 90%, the font size became 122 (LH1Size100 = font size 11, LH1Size100 + 1 = font size 12, LH1Text “2 August 2008” = 12+2 (122). I have been trying (and failing) to fix the code so that I can enter numbers at the beginning of the line without blowing up the font size. (They are not going to change their method of writing the date.) Does anyone with more experience/knowledge know how to fix this code?

Much of this code has been deleted to fit this forum's thread size requirements:

Sub test3000()

Dim s As Worksheet

Dim LH1Size As String
Dim LH2Size As String
Dim CH1Size As String
Dim CH2Size As String
Dim RH1Size As String
Dim RH2Size As String
Dim LF1Size As String
Dim LF2Size As String
Dim CF1Size As String
Dim CF2Size As String
Dim RF1Size As String
Dim RF2Size As String
Dim LH As String
Dim CH As String
Dim RH As String
Dim LF As String
Dim CF As String
Dim RF As String

Dim LH1Size100, Var1
Var1 = "&"

LH1Size100 = 11
LH2Size100 = 11

CH1Size100 = 11
CH2Size100 = 11

RH1Size100 = 11
RH2Size100 = 11

LF1Size100 = 11
LF2Size100 = 10

CF1Size100 = 8
CF2Size100 = 8

RF1Size100 = 11
RF2Size100 = 11

LH1Font = "&""Arial Narrow,Italic"""
LH2Font = ""

CH1Font = "&""Arial Narrow,Italic"""
CH2Font = ""

RH1Font = "&""Arial Narrow,Italic"""
RH2Font = ""

LF1Font = "&""Arial,Regular"""
LF2Font = ""

CF1Font = "&""Arial Narrow,Italic"""
CF2Font = ""

RF1Font = "&""Arial Narrow,Regular"""
RF2Font = ""

LH1Text = "2 August 2008" & Chr(10)
LH2Text = "Left Header Line 2"

CH1Text = "Center Header Line 1" & Chr(10)
CH2Text = "Center Header Line 2"

RH1Text = "Right Header Line 1" & Chr(10)
RH2Text = "Right Header Line 2"

CF1Text = "Center Footer Line 1" & Chr(10)
CF2Text = "Center Footer Line 2"

LF1Text = "Left Footer Line 1" & Chr(10)
LF2Text = "Left Footer Line 2"

RF1Text = "&P"
RF2Text = ""

For Each s In ActiveWorkbook.Worksheets
With s.PageSetup

If s.PageSetup.Zoom = 100 Then LH1Size = Var1 & LH1Size100
If s.PageSetup.Zoom = 100 Then LH2Size = Var1 & LH2Size100
If s.PageSetup.Zoom = 100 Then CH1Size = Var1 & CH1Size100
If s.PageSetup.Zoom = 100 Then CH2Size = Var1 & CH2Size100
If s.PageSetup.Zoom = 100 Then RH1Size = Var1 & RH1Size100
If s.PageSetup.Zoom = 100 Then RH2Size = Var1 & RH2Size100
If s.PageSetup.Zoom = 100 Then LF1Size = Var1 & LF1Size100
If s.PageSetup.Zoom = 100 Then LF2Size = Var1 & LF2Size100
If s.PageSetup.Zoom = 100 Then CF1Size = Var1 & CF1Size100
If s.PageSetup.Zoom = 100 Then CF2Size = Var1 & CF2Size100
If s.PageSetup.Zoom = 100 Then RF1Size = Var1 & RF1Size100
If s.PageSetup.Zoom = 100 Then RF2Size = Var1 & RF2Size100

‘ deleted 99 through 91

If s.PageSetup.Zoom = 90 Then LH1Size = Var1 & LH1Size100 + 1
If s.PageSetup.Zoom = 90 Then LH2Size = Var1 & LH2Size100 + 1
If s.PageSetup.Zoom = 90 Then CH1Size = Var1 & CH1Size100 + 1
If s.PageSetup.Zoom = 90 Then CH2Size = Var1 & CH2Size100 + 1
If s.PageSetup.Zoom = 90 Then RH1Size = Var1 & RH1Size100 + 1
If s.PageSetup.Zoom = 90 Then RH2Size = Var1 & RH2Size100 + 1
If s.PageSetup.Zoom = 90 Then LF1Size = Var1 & LF1Size100 + 1
If s.PageSetup.Zoom = 90 Then LF2Size = Var1 & LF2Size100 + 1
If s.PageSetup.Zoom = 90 Then CF1Size = Var1 & CF1Size100 + 1
If s.PageSetup.Zoom = 90 Then CF2Size = Var1 & CF2Size100 + 1
If s.PageSetup.Zoom = 90 Then RF1Size = Var1 & RF1Size100 + 1
If s.PageSetup.Zoom = 90 Then RF2Size = Var1 & RF2Size100 + 1

‘ deleted 89 through 81

If s.PageSetup.Zoom = 80 Then LH1Size = Var1 & LH1Size100 + 2
If s.PageSetup.Zoom = 80 Then LH2Size = Var1 & LH2Size100 + 2
If s.PageSetup.Zoom = 80 Then CH1Size = Var1 & CH1Size100 + 2
If s.PageSetup.Zoom = 80 Then CH2Size = Var1 & CH2Size100 + 2
If s.PageSetup.Zoom = 80 Then RH1Size = Var1 & RH1Size100 + 2
If s.PageSetup.Zoom = 80 Then RH2Size = Var1 & RH2Size100 + 2
If s.PageSetup.Zoom = 80 Then LF1Size = Var1 & LF1Size100 + 2
If s.PageSetup.Zoom = 80 Then LF2Size = Var1 & LF2Size100 + 2
If s.PageSetup.Zoom = 80 Then CF1Size = Var1 & CF1Size100 + 2
If s.PageSetup.Zoom = 80 Then CF2Size = Var1 & CF2Size100 + 2
If s.PageSetup.Zoom = 80 Then RF1Size = Var1 & RF1Size100 + 2
If s.PageSetup.Zoom = 80 Then RF2Size = Var1 & RF2Size100 + 2

‘ deleted 79 through 42

If s.PageSetup.Zoom = 41 Then LH1Size = Var1 & LH1Size100 + 11
If s.PageSetup.Zoom = 41 Then LH2Size = Var1 & LH2Size100 + 11
If s.PageSetup.Zoom = 41 Then CH1Size = Var1 & CH1Size100 + 11
If s.PageSetup.Zoom = 41 Then CH2Size = Var1 & CH2Size100 + 11
If s.PageSetup.Zoom = 41 Then RH1Size = Var1 & RH1Size100 + 11
If s.PageSetup.Zoom = 41 Then RH2Size = Var1 & RH2Size100 + 11
If s.PageSetup.Zoom = 41 Then LF1Size = Var1 & LF1Size100 + 11
If s.PageSetup.Zoom = 41 Then LF2Size = Var1 & LF2Size100 + 11
If s.PageSetup.Zoom = 41 Then CF1Size = Var1 & CF1Size100 + 11
If s.PageSetup.Zoom = 41 Then CF2Size = Var1 & CF2Size100 + 11
If s.PageSetup.Zoom = 41 Then RF1Size = Var1 & RF1Size100 + 11
If s.PageSetup.Zoom = 41 Then RF2Size = Var1 & RF2Size100 + 11

If s.PageSetup.Zoom = 40 Then LH1Size = Var1 & LH1Size100 + 12
If s.PageSetup.Zoom = 40 Then LH2Size = Var1 & LH2Size100 + 12
If s.PageSetup.Zoom = 40 Then CH1Size = Var1 & CH1Size100 + 12
If s.PageSetup.Zoom = 40 Then CH2Size = Var1 & CH2Size100 + 12
If s.PageSetup.Zoom = 40 Then RH1Size = Var1 & RH1Size100 + 12
If s.PageSetup.Zoom = 40 Then RH2Size = Var1 & RH2Size100 + 12
If s.PageSetup.Zoom = 40 Then LF1Size = Var1 & LF1Size100 + 12
If s.PageSetup.Zoom = 40 Then LF2Size = Var1 & LF2Size100 + 12
If s.PageSetup.Zoom = 40 Then CF1Size = Var1 & CF1Size100 + 12
If s.PageSetup.Zoom = 40 Then CF2Size = Var1 & CF2Size100 + 12
If s.PageSetup.Zoom = 40 Then RF1Size = Var1 & RF1Size100 + 12
If s.PageSetup.Zoom = 40 Then RF2Size = Var1 & RF2Size100 + 12

LH = LH1Font & LH1Size & LH1Text & LH2Font & LH2Size & LH2Text
CH = CH1Font & CH1Size & CH1Text & CH2Font & CH2Size & CH2Text
RH = RH1Font & RH1Size & RH1Text & RH2Font & RH2Size & RH2Text
LF = LF1Font & LF1Size & LF1Text & LF2Font & LF2Size & LF2Text
CF = CF1Font & CF1Size & CF1Text & CF2Font & CF2Size & CF2Text
RF = RF1Font & RF1Size & RF1Text & RF2Font & RF2Size & RF2Text

.LeftHeader = LH
.CenterHeader = CH
.RightHeader = RH
.LeftFooter = LF
.CenterFooter = CF
.RightFooter = RF

End With
Next s

End Sub

SunD3 · Jan 26, 2009

This post has been solved. The macro now works properly.

fumei · Jan 26, 2009

That is good. I would like to point out however that your IF statements could be done much better as ONE logic statement.

Code:

If s.PageSetup.Zoom = 40 Then LH1Size = Var1 & LH1Size100 + 12
If s.PageSetup.Zoom = 40 Then LH2Size = Var1 & LH2Size100 + 12
If s.PageSetup.Zoom = 40 Then CH1Size = Var1 & CH1Size100 + 12
If s.PageSetup.Zoom = 40 Then CH2Size = Var1 & CH2Size100 + 12
If s.PageSetup.Zoom = 40 Then RH1Size = Var1 & RH1Size100 + 12
If s.PageSetup.Zoom = 40 Then RH2Size = Var1 & RH2Size100 + 12
If s.PageSetup.Zoom = 40 Then LF1Size = Var1 & LF1Size100 + 12
If s.PageSetup.Zoom = 40 Then LF2Size = Var1 & LF2Size100 + 12
If s.PageSetup.Zoom = 40 Then CF1Size = Var1 & CF1Size100 + 12
If s.PageSetup.Zoom = 40 Then CF2Size = Var1 & CF2Size100 + 12
If s.PageSetup.Zoom = 40 Then RF1Size = Var1 & RF1Size100 + 12
If s.PageSetup.Zoom = 40 Then RF2Size = Var1 & RF2Size100 + 12

is 12 separate, parsed and executed instructions. That is, VBA actually checks the value of s.PageSetup.Zoom 12 separate times - for EACH If statement.

Code:

If s.PageSetup.Zoom = 40 Then
   LH1Size = Var1 & LH1Size100 + 12
   LH2Size = Var1 & LH2Size100 + 12
   CH1Size = Var1 & CH1Size100 + 12
   CH2Size = Var1 & CH2Size100 + 12
   RH1Size = Var1 & RH1Size100 + 12
   RH2Size = Var1 & RH2Size100 + 12
   LF1Size = Var1 & LF1Size100 + 12
   LF2Size = Var1 & LF2Size100 + 12
   CF1Size = Var1 & CF1Size100 + 12
   CF2Size = Var1 & CF2Size100 + 12
   RF1Size = Var1 & RF1Size100 + 12
   RF2Size = Var1 & RF2Size100 + 12
End If

only tests the value of s.PageSetup.Zoom once.

Better yet, use Select Case on s.PageSetup.Zoom itself!

As it stand you have 60 If..Then statements, and each one is executed regardless of any previous If..Then. That means, say the first one comes out True:

Code:

If s.PageSetup.Zoom = 100 Then

.Zoom IS 100.

Even though .Zoom = 100, ALL of the other If..Then are still parsed and executed.

.Zoom evaluated and tested.

Regardless of the value of .Zoom,

Code:

....' lots of other executed lines
If s.PageSetup.Zoom = 41 Then CF2Size = Var1 & CF2Size100 + 11
If s.PageSetup.Zoom = 41 Then RF1Size = Var1 & RF1Size100 + 11
If s.PageSetup.Zoom = 41 Then RF2Size = Var1 & RF2Size100 + 11

If s.PageSetup.Zoom = 40 Then LH1Size = Var1 & LH1Size100 + 12
If s.PageSetup.Zoom = 40 Then LH2Size = Var1 & LH2Size100 + 12
If s.PageSetup.Zoom = 40 Then CH1Size = Var1 & CH1Size100 + 12
If s.PageSetup.Zoom = 40 Then CH2Size = Var1 & CH2Size100 + 12

...more executed lines...

are still executed. This is worth repeating.

If .Zoom = 100 then

Code:

... lots of others
If s.PageSetup.Zoom = 41 Then CF2Size = Var1 & CF2Size100 + 11
.. lots of others

is STILL executed.

60 separate parsed and executed lines of instructions testing the value of s.PageSetup.Zoom. If you use Select Case, like this:

Code:

Select Case s.PageSetup.Zoom
   Case 100
      LH1Size = Var1 & LH1Size100
      LH2Size = Var1 & LH2Size100
      CH1Size = Var1 & CH1Size100
      CH2Size = Var1 & CH2Size100
      RH1Size = Var1 & RH1Size100
      RH2Size = Var1 & RH2Size100
      LF1Size = Var1 & LF1Size100
      LF2Size = Var1 & LF2Size100
      CF1Size = Var1 & CF1Size100
      CF2Size = Var1 & CF2Size100
      RF1Size = Var1 & RF1Size100
      RF2Size = Var1 & RF2Size100
      
   ' deleted 99 through 91
   
   Case 90
      LH1Size = Var1 & LH1Size100 + 1
      LH2Size = Var1 & LH2Size100 + 1
      CH1Size = Var1 & CH1Size100 + 1
      CH2Size = Var1 & CH2Size100 + 1
      RH1Size = Var1 & RH1Size100 + 1
      RH2Size = Var1 & RH2Size100 + 1
      LF1Size = Var1 & LF1Size100 + 1
      LF2Size = Var1 & LF2Size100 + 1
      CF1Size = Var1 & CF1Size100 + 1
      CF2Size = Var1 & CF2Size100 + 1
      RF1Size = Var1 & RF1Size100 + 1
      RF2Size = Var1 & RF2Size100 + 1

' deleted 89 through 81
   
   Case 80
      LH1Size = Var1 & LH1Size100 + 2
      LH2Size = Var1 & LH2Size100 + 2
      CH1Size = Var1 & CH1Size100 + 2
      CH2Size = Var1 & CH2Size100 + 2
      RH1Size = Var1 & RH1Size100 + 2
      RH2Size = Var1 & RH2Size100 + 2
      LF1Size = Var1 & LF1Size100 + 2
      LF2Size = Var1 & LF2Size100 + 2
      CF1Size = Var1 & CF1Size100 + 2
      CF2Size = Var1 & CF2Size100 + 2
      RF1Size = Var1 & RF1Size100 + 2
      RF2Size = Var1 & RF2Size100 + 2

' deleted 79 through 42

   Case 41
      LH1Size = Var1 & LH1Size100 + 11
      LH2Size = Var1 & LH2Size100 + 11
      CH1Size = Var1 & CH1Size100 + 11
      CH2Size = Var1 & CH2Size100 + 11
      RH1Size = Var1 & RH1Size100 + 11
      RH2Size = Var1 & RH2Size100 + 11
      LF1Size = Var1 & LF1Size100 + 11
      LF2Size = Var1 & LF2Size100 + 11
      CF1Size = Var1 & CF1Size100 + 11
      CF2Size = Var1 & CF2Size100 + 11
      RF1Size = Var1 & RF1Size100 + 11
      RF2Size = Var1 & RF2Size100 + 11

   Case 40
      LH1Size = Var1 & LH1Size100 + 12
      LH2Size = Var1 & LH2Size100 + 12
      CH1Size = Var1 & CH1Size100 + 12
      CH2Size = Var1 & CH2Size100 + 12
      RH1Size = Var1 & RH1Size100 + 12
      RH2Size = Var1 & RH2Size100 + 12
      LF1Size = Var1 & LF1Size100 + 12
      LF2Size = Var1 & LF2Size100 + 12
      CF1Size = Var1 & CF1Size100 + 12
      CF2Size = Var1 & CF2Size100 + 12
      RF1Size = Var1 & RF1Size100 + 12
      RF2Size = Var1 & RF2Size100 + 12
End Select

there is ONE test of the value of s.PageSetup.Zoom, and instructions carried according to that value.

So if .Zoom = 100, all those Case 100 instructions are done...and nothing more.

Gerry

combo · Jan 27, 2009

Your code has two gaps:
- what should happen when the Zoom is different than handled in If/Select Case conditions?
- what should happen when 'fit to pages' option is set (Zoom=False)?
To simplify the code I would rather split code into two parts: depending on Zoom determine font correction (no 'False' case considered) and apply formatting later. Intermediate zooms are covered by 'Case' conditions:

Code:

Select Case s.PageSetup.Zoom
   Case 100
      SizeCorrection = 0
   Case 90 To 99
      SizeCorrection = 1
   Case 80 To 89
      SizeCorrection = 2
   Case 41
      SizeCorrection = 11
   Case 40
      SizeCorrection = 12
End Select
LH1Size = Var1 & LH1Size100 + SizeCorrection
LH2Size = Var1 & LH2Size100 + SizeCorrection
CH1Size = Var1 & CH1Size100 + SizeCorrection
CH2Size = Var1 & CH2Size100 + SizeCorrection
RH1Size = Var1 & RH1Size100 + SizeCorrection
RH2Size = Var1 & RH2Size100 + SizeCorrection
LF1Size = Var1 & LF1Size100 + SizeCorrection
LF2Size = Var1 & LF2Size100 + SizeCorrection
CF1Size = Var1 & CF1Size100 + SizeCorrection
CF2Size = Var1 & CF2Size100 + SizeCorrection
RF1Size = Var1 & RF1Size100 + SizeCorrection
RF2Size = Var1 & RF2Size100 + SizeCorrection

combo

fumei · Jan 27, 2009

Excuse me?

I was simply pointing out that 60 If...Then statements as it was written by the OP could be done better as a one Select Case.

"- what should happen when the Zoom is different than handled in If/Select Case conditions?"

Huh? If that sort of logic had been in the OP's code, I would have included such in mine. There is no "gap", as the OP did not consider such a condition. Should such a condition BE considered? Should it be considerd as a possible "gap"? Yes.

Besides, if Zoom is different from the explicit Case conditions, then that is what Case Else is for.

I was just trying to point out that:

1. using (in the given example) 60 executed instructions when ONE can possibly do it, is inefficient.

2. Separate If...Then statements are executed as separate instructions, regardless of any previous If...Then instructions. They are separate logic instructions.

Again, that if .Zoom is 100, all the If...Then for .Zoom = 40 are still executed.

I do like that you suggested using a variable (SizeCorrection). It could also be better if an array was used for other things like the text, or the fonts.

Gerry

combo · Jan 27, 2009

Don't take it personally, Gerry. Most of my post referred to the original problem rather than your observations. I pointed potential gaps in the code when setting header/footer. I also suggested a way to shorten the code.
I understand this site as a place where you can freely share tips linked to the original thread, quite often in random order.

combo

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

scaling font size in headers and footers

SunD3

Technical User

SunD3

Technical User

fumei

Technical User

combo

Technical User

fumei

Technical User

combo

Technical User

Similar threads

Part and Inventory Search

Sponsor