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
'.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
End With

' 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,


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!


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question