Modifying a footer date

[Guest]

First, just to clarify, I am NOT looking to print the date/time the file was
last modified, but rather I am looking to modify the default date format that
Excel displays on the footers. How can this be done?

Example:
Currently the date looks like this: 10/21/05
I would like the date to look like this: Friday, October 21, 2005

Thanks in advance,

Jim

 

[Bob Phillips]

If you do use the print the date technique you can specify its format


Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = Format(Date, "dddd, mmmm dd, yyyy")
End With
End Sub
'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hello
I would like something similar. In the center footer (I've already modified
'Left" to "center' in the string), I need a line of text in the center footer
with the text "Last Printed 25-OCT-2005" (for example) on the second line of
the footer. So it would read in center footer:
Effective date - whatever text I type - not an updatable field
Last Printed &[Date]
(where the format must be dd-MMM-yyyy)

When I use Bob's string below, it's overwriting my first line of code.

My thx. in advance for your help.

Mary

 

[Bob Phillips]

Mary,

Try this

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sTemp As String
Dim iPos As Long
With ActiveSheet.PageSetup
sTemp = .LeftFooter
iPos = InStr(sTemp, "Last Printed:")
If iPos > 0 Then
sTemp = Left(sTemp, iPos - 1)
End If
.LeftFooter = sTemp & vbNewLine & _
"Last Printed: " & Format(Date, "dd-mmm-yyyy")
End With
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

Hello
I would like something similar. In the center footer (I've already modified
'Left" to "center' in the string), I need a line of text in the center footer
with the text "Last Printed 25-OCT-2005" (for example) on the second line of
the footer. So it would read in center footer:
Effective date - whatever text I type - not an updatable field
Last Printed &[Date]
(where the format must be dd-MMM-yyyy)

When I use Bob's string below, it's overwriting my first line of code.

My thx. in advance for your help.

Mary

If you do use the print the date technique you can specify its format


Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = Format(Date, "dddd, mmmm dd, yyyy")
End With
End Sub
'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)


file
was format
that

 

[Guest]

Many thanks Bob. I'll try tonight & let you know. THX for your quick
response!

Mary

Mary,

Try this

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sTemp As String
Dim iPos As Long
With ActiveSheet.PageSetup
sTemp = .LeftFooter
iPos = InStr(sTemp, "Last Printed:")
If iPos > 0 Then
sTemp = Left(sTemp, iPos - 1)
End If
.LeftFooter = sTemp & vbNewLine & _
"Last Printed: " & Format(Date, "dd-mmm-yyyy")
End With
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

Hello
I would like something similar. In the center footer (I've already modified
'Left" to "center' in the string), I need a line of text in the center footer
with the text "Last Printed 25-OCT-2005" (for example) on the second line of
the footer. So it would read in center footer:
Effective date - whatever text I type - not an updatable field
Last Printed &[Date]
(where the format must be dd-MMM-yyyy)

When I use Bob's string below, it's overwriting my first line of code.

My thx. in advance for your help.

Mary

If you do use the print the date technique you can specify its format


Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = Format(Date, "dddd, mmmm dd, yyyy")
End With
End Sub
'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)


First, just to clarify, I am NOT looking to print the date/time the file
was
last modified, but rather I am looking to modify the default date format
that
Excel displays on the footers. How can this be done?

Example:
Currently the date looks like this: 10/21/05
I would like the date to look like this: Friday, October 21, 2005

Thanks in advance,

Jim

 

[Guest]

Hi again

So ... I think I'm close. I modified the code to be CenterFooter - that's
fine.

But I'm getting an extra linefeed that I don't want. What I do want in the
centre footer is:

Effective Date - (a date manually entered by me - fot a field)
Last Printed: (then, last printed date)

There should only be one line feed after Effective date.

I cut out the "& vbNewLine" but it's still doing it.

Any advice?

Mary

Mary,

Try this

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sTemp As String
Dim iPos As Long
With ActiveSheet.PageSetup
sTemp = .LeftFooter
iPos = InStr(sTemp, "Last Printed:")
If iPos > 0 Then
sTemp = Left(sTemp, iPos - 1)
End If
.LeftFooter = sTemp & vbNewLine & _
"Last Printed: " & Format(Date, "dd-mmm-yyyy")
End With
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

Hello
I would like something similar. In the center footer (I've already modified
'Left" to "center' in the string), I need a line of text in the center footer
with the text "Last Printed 25-OCT-2005" (for example) on the second line of
the footer. So it would read in center footer:
Effective date - whatever text I type - not an updatable field
Last Printed &[Date]
(where the format must be dd-MMM-yyyy)

When I use Bob's string below, it's overwriting my first line of code.

My thx. in advance for your help.

Mary

If you do use the print the date technique you can specify its format


Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = Format(Date, "dddd, mmmm dd, yyyy")
End With
End Sub
'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)


First, just to clarify, I am NOT looking to print the date/time the file
was
last modified, but rather I am looking to modify the default date format
that
Excel displays on the footers. How can this be done?

Example:
Currently the date looks like this: 10/21/05
I would like the date to look like this: Friday, October 21, 2005

Thanks in advance,

Jim