Can't apply formating to header when a variable is used to represent cell contents

[MS Newsgroups]

My cell D1 is used to represent the text to be used in CenterHeader. I have
created a variable called centerheaderText to represent the contents of D1.
Whether I use the centerheaderText variable or a direct reference such as
Worksheets("Calendar").Range("D1")..Value, I cannot apply formating.
However, if I embed the text in my code or use an Input Box I can.

As you can see in my code comments below, I can't apply formating to the
CenterHeader, only to a text string or Input Box.

*************************************************
Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim ws As Worksheet

For Each ws In Worksheets

'Formating attempt
With ActiveSheet.PageSetup
Dim centerheaderText As String
centerheaderText = Worksheets("Calendar").Range("D1").Value
'MsgBox centerheaderText
'SOURCE: http://www.ozgrid.com/forum/showthread.php?t=16944
'.CenterHeader = "&""Times New Roman,Regular""&20" &
centerheaderText 'Doesn't work
'.CenterHeader = "&""Times New Roman,Bold""&20" &
Worksheets("Calendar").Range("D1").Value 'Doesn't work
'.CenterHeader = "&""Times New Roman,Bold""&20" &
Worksheets("Calendar").Range("D1").Text 'Doesn't work
.CenterHeader = "&""Times New Roman,Bold""&20" & "T E S T I
N G" 'WORKS
End With

'This WORKS SOURCE:
http://www.ozgrid.com/forum/showthread.php?t=16944
' With ActiveSheet.PageSetup
' Dim MyInput
' MyInput = InputBox("Enter Report title")
' .CenterHeader = "&""Times New Roman,Bold""&26" & MyInput
' End With

Next ws

End Sub
*************************************************

Thank you,

Ken

 

[Dave Peterson]

What's in that cell?

If it starts with a number, then put a space character after the font size:

..CenterHeader = "&""Times New Roman,Regular""&20 " & centerheaderText

This kind of thing:
..CenterHeader = "&""Times New Roman,Regular""&20" & "3123.232"
would try to make a header with a huge font!

 

[MS Newsgroups]

Thanks for replying Dave.

OMG, I have spend HOURS on this and surfed several Google pages of sites,
including Excel Help, and no where was there a mention or caution regarding
the requirement to add a space after the font size. I works!!

THANK YOU (yes I'm shouting)!!

Have a great weekend!

Ken