VBA to Size, Bold, and Font a header with Cell Value

[jdorby]

Hi All,

I'm trying to pass this code to a header:

'Range("C4").Value = Department_Name

strHeader = Cells.Range("C4").Value
With ActiveSheet.PageSetup
.PrintTitleRows = "$6:$8"
.CenterHeader = "&""Calibri,Bold""&28" & strHeader
.CenterFooter = "&P"
.LeftMargin = Application.InchesToPoints(0.2)
.RightMargin = Application.InchesToPoints(0.2)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.25)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.25)
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.OddAndEvenPagesHeaderFooter = False
End With

End Sub

For some reason, everything works, but my Font Size ends up being 409 pt size and I can't figure out why. Any help is appreciated.

Thanks!

Jim

 

[jdorby]

Someone please help!!

 

[Norie]

Jim

What's actually in C4?

 

[jdorby]

Hi Norie! I never got a message saying you replied or I would have responded a month ago!

C4 is a formula written as:  =CONCATENATE(C3," - ",TEXT(C1,"MMMM YYYY"))

C4 ends up displaying:  26.00 International Operations - September 2014

All I'm trying to do is put that cell value in the center header with bold formatting, 28 size font, and calibri(body) font.

Thank you in advance for any help!

 

[Norie]

The reason you could be getting the super large font size could be because the value in C4 starts with 26.00 and that's being concatenated to the end of 28 which I assume is the font size you want.

There's a simple fix - put a space in front of 26.00.

.CenterHeader = "&""Calibri,Bold""&28" & " 26.00 International Operations - September 2014"

 

[jdorby]

WOW - note to self, never start a string off with a number. You're brilliant! Thank you for the quick response! Have a great day!

 

[Norie]

There's nothing wrong with starting a string off with a number, it just so happens in this case that the number was being included in with the formatting.