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!

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

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.

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

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

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!

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

D'oh!

Thank you (and Dick) 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!