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

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!

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
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"Ronny Hamida" wrote:

> 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!
>

Ronny Hamida wrote:

> 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.

--
**** Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

D'oh!

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

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

"sebastienm" wrote:

> 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
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "Ronny Hamida" wrote:
>
> > 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!
> >