adding the username to a header

Question

Within 'Page Setup - Header/Footer - custom header', is it possible to add the username thus:

Printed on &[Date]

at &[Time]

by &[UserName]

where UserName is determined by the following function in a module:

Function UserName()

        UserName = Environ("UserName")

End Function 

Thanks

Answer 1

You can't use &[UserName] - it won't be recognized. But you can use the BeforePrint event of the workbook (in the ThisWorkbook module) to set the header or footer using VBA. For example:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Dim wsh As Worksheet

    For Each wsh In Me.Worksheets

        wsh.PageSetup.CenterFooter = "Printed on &D at &T by " & Environ("Username")

    Next wsh

End Sub

Answer 2

HansV,

I have managed to get this to work by adding it to a control on the user form rather than using the BeforePrint event.

Thanks.

Answer 3

HansV,

Thanks for this. I do get an error message tho:

"unable to set the CentreFooter property of the PageSetup class"

I've tried just text rather than date, time and user just to see if that would work but I got the same message.