Date fixed in Header

PaulJL

New Member
Joined
Oct 9, 2002
Messages
48
I prepare daily reports for a client. I currently use the date button to place the date of the report in the header. If i need to print this report at a later date, the date in the header defaults to the current date and not the date the report was prepared. Is there anyway of fixing the date in the header once it has been inputed? I have searched used the search option on the site and cannot find a relevant topic.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

JamesKM

Board Regular
Joined
Aug 12, 2002
Messages
130
Hi Paul,

I have a very similar thing and have created a little work around that requires manual entry of todays date. Code follows, but I would love to find a solution that inputs todays date for you and then does not update.

Sub MakeHeader()

Dim HeaderText As String
HeaderText = InputBox("Please fill in Todays Date in the same Format at the end of the Default Text", "Header Text", "ANY TEXT YOU WANT FOLLOWED BY DD/MM/YYYY")
ActiveSheet.PageSetup.CenterHeader = "&""Arial,Bold""&16" & HeaderText

End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This will do it:

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim Answer
    Dim D As String
    Dim T As String
    Answer = MsgBox("Do you want to update the date in the header?", vbYesNo + vbQuestion)
    If Answer = vbYes Then
        D = Format(Date, "Short Date")
        T = Format(Time, "Short Time")
        ActiveSheet.PageSetup.CenterHeader = D & " " & T
    End If
End Sub
 

Forum statistics

Threads
1,143,915
Messages
5,721,520
Members
422,369
Latest member
redinator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top