# UserName in Footer - Excel



## boysclubx3 (Oct 3, 2008)

I've been hunting google for hours. I have found 2 or 3 solutions to entering VBA code for putting the username in the footer automatically. However, I put the code in and it doesn't work. I'm thinking the issue may be that several of the solutions are for earlier excel versions (95 and 03).

Does anyone know how to do this? I'm building a template excel spreadsheet for my boss.

Thank you!

Christine


----------



## boysclubx3 (Oct 3, 2008)

Ok, I finally found code that works, see here:

Private Sub Workbook_BeforePrint(Cancel As Boolean) 
With ActiveSheet.PageSetup 
.CenterHeader = Range("CompHeader") 
.LeftFooter = "Statement Prepared by " & Application.UserName & "on &D" 
End With 

End Sub 

(Found at: http://www.mrexcel.com/archive2/11700/13145.htm)

HOWEVER... when I place it on our shared drive, it no longer recognizes the username - instead it recognizes the company name (presumably from the computer's "name" information here). So I reformatted the above to say "Prepared at Company S by UserName on Date" and now it says "Prepared at Company S by Company S on Date" when someone opens the document.

To add a twist, I made this an xlt (template). Could I have gone wrong there?

Thank you,

Christine


----------



## Glaswegian (Sep 16, 2005)

Hi and welcome.

I tend to use the following code to return the username. It was originally intended for Win NT but since XP is NT based, it works fine.

This code should go in its own Module:

```
Option Explicit

Private Declare Function WNetGetUser Lib "mpr.dll" Alias "WNetGetUserA" (ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long
Private Const NO_ERROR = 0
Private Const ERROR_NOT_CONNECTED = 2250&
Private Const ERROR_MORE_DATA = 234
Private Const ERROR_NO_NETWORK = 1222&
Private Const ERROR_EXTENDED_ERROR = 1208&
Private Const ERROR_NO_NET_OR_BAD_PATH = 1203&

Sub add_username_to_footer()
'this adds the username from the NT loggon to the footer of a print
Dim strBuf As String, lngUser As Long, strUn As String
    strBuf = Space$(255) '//Clear buffer
    lngUser = WNetGetUser("", strBuf, 255)
    If lngUser = NO_ERROR Then
        strUn = Left(strBuf, InStr(strBuf, vbNullChar) - 1)
    Else
    End If
    
    With ActiveSheet.PageSetup
        .LeftFooter = ""
        .LeftFooter = "Statement Prepared by " & "&8User: " & strUn & "on &D"
    End With
End Sub
```
I've included your code - adjust to suit. Just call the sub *add_username_to_footer* as part of your print routine.


----------



## boysclubx3 (Oct 3, 2008)

Thank you - I'm going to try yours! I was able to borrow a macro that forces the user to input their user name on a form, then it transfers that information to the code that puts it in the footer. I'd rather have something that does it automatically to avoid people over-riding the macro, so yours may be better for me. Thanks again!


----------



## boysclubx3 (Oct 3, 2008)

Well, I can't get it to work? I put it in it's own module, it didn't work. I copied it into the workbook in case it needed to be in the current workbook, that did not work either.

I'm sure it's user error, I do not know very much about VBA at all - just a little. I'll keep messing with it - that's how I got it to work last time, I just messed wtih it until I found where I'd gone wrong!


----------



## Glaswegian (Sep 16, 2005)

Hi

The whole idea of the code is that everything is done automatically - no user input required. Are you getting anything back at all? Have you remembered to call this routine within your main print routine? Actually, how are you printing? If it's just via the 'Print' icon then this will not work. I use a command button or similar to force users to click on that when printing - you can then control what is printed and the overall look.


----------



## boysclubx3 (Oct 3, 2008)

Ok that explains it. This will be used by several people who are not familiar with VBA. Basically I'm setting this up as a template for our department. I can make a command button - I'll have to try that later on.

I have to go post another question. I'm getting asked to make excel stand on it's head and sing the ABC's - oye.


----------

