Can't insert a String Variable of Long Date into Page Header

[Guest]

I think this might be an easy one...

I have this code in my main Workbook to retrieve the date from cell C17 and
keep it as a String Variable called TheDate:

Dim TheDate As String

If Range("C17").Value = "" Then
Range("C17").Select
MissingDate = MsgBox("Please input the date into the date box.",
vbOKOnly, "Missing Date")
End
Else
TheDate = Format(Range("C17").Value, "mmmm d, yyyy")
End If

I have then checked the variable, TheDate, to make sure that it retained the
information as needed. For instance, if someone enters 4/3/2006 into cell
C17, TheDate is then equal to "April 3, 2006".

When I try to put this into my page header, all I get is the "2006" from
this variable. Here's a sample of what I have to populate my page header:


With ActiveSheet.PageSetup
.CenterHeader = _
"&""Arial,Bold""&12Employee Roster" & Chr(10) & "&""Arial,Regular" &
TheDate
End With

If the string variable, TheDate, is equal to "April 3, 2006" then why do I
only get "2006" in the header where the full value should be?

I have even tried to change the variable type to Date (Dim TheDate as Date)
and work with it that way, but it just disappears.

Any thoughts?

Thank you!

 

[Dick Kusleika]

With ActiveSheet.PageSetup
.CenterHeader = _
"&""Arial,Bold""&12Employee Roster" & Chr(10) &
"&""Arial,Regular" & TheDate
End With

Ronny:

You need ..."&""Arial,Regular""" & TheDate

which includes the double quotes after Arial, Regular.

 

[Guest]

Hi,
You forgot to close the second font definition with a double-quote. It
should be:
"&""Arial,Bold""&12Employee Roster" & Chr(10) & "&""Arial,Regular""" & TheDate

 

[Guest]

D'oh!

Thank you (and Dick) for the prompt reply...

It worked. Next, I'll get new glasses...