Date fixed in Header

[PaulJL]

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.

 

[JamesKM]

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]

This will do it:

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

 

[PaulJL]

Thanks Andrew/James,

That has solved the problem

Regards

Paul