Keep Original Username in Header

[mllex]

I have the following code in the This Workbook - BeforePrint. It prints the username in the right header.

It works create if the user prints out their Excel spreadsheet and then delivers it to another department.

However, we want to email the spreadsheet to another department, but have the original author's name still appear in the header when it prints. The way it is set up now the recipient's name will print in the header when they open the file and print the output.

Does anyone have a solution? I know any email would indicate who the file came from, but I want this information on the printed output as well.

TIA

Dim ws As Worksheet
For Each ws In Worksheets
ws.PageSetup.RightHeader = "&7" & Application.UserName
Next ws
End Sub

 

[acron]

As once the header is in place, it does not need to change so you can remove the code. If nothing is going to change there is no need to change the header each time you print you the file.

AC

 

[SkipVought]

Hi,
You could have a hidden sheet that has a single value in A1 named "NameOnHeader".

In your code, you substitute that value...
ws.PageSetup.RightHeader = "&7" & Range("NameOnHeader&quot

Then you have some other code that controls what Name is placed in "NameOnHeader", base on whether the eMail function has been called (the originator's name) or not (the Aplication.UserName).

Let me know if this helps Skip,
metzgsk@voughtaircraft.com

 

[mllex]

I'm not sure I follow you, acron. You may be on to something, I just don't get it.

Skip, I think your idea will work. I'll give it a whirl.

Many thanks to you both!

 

[acron]

I am just making the point that once your header value is set, it will remnain in place without any further need for the BeforePrint event. You only newed to set the header once if you want it to remain unchanged.

AC

 

[mllex]

Ok acron, I'm with you now, but how do I delete the code in the BeforePrint event in the email?

 

[acron]

How to delete the code woulld depend on what other code you have in the ThisWorkbook code module. Can you advise on that ? It is relatively easy if you have no other code.

In the meantime consider :

    For Each ws In Worksheets
        If Len(ws.PageSetup.RightHeader) = 0 Then
            ws.PageSetup.RightHeader = _
                "&7" & Application.UserName
        End If
    Next ws

AC

 

[mllex]

Brilliant!

I think that's just the ticket.

Thank you!