Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2007
    Thanked 0 Times in 0 Posts

    Dynamic Footers (MS Excel 2003)

    I am trying to capture and print LAN Credentials (User ID's) on a footer.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    A Magic Forest in Deepest, Darkest Kent
    Thanked 1 Time in 1 Post

    Re: Dynamic Footers (MS Excel 2003)

    Hi Eric

    Welcome to the Lounge!

    Here is some code you could put in a Module

    Public Declare Function GetUserName Lib "advapi32.dll" _
    Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

    Function ReturnUserName() As String
    ' returns the NT Domain User Name
    Dim rString As String * 255, sLen As Long, tString As String
    tString = ""
    On Error Resume Next
    sLen = GetUserName(rString, 255)
    sLen = InStr(1, rString, Chr(0))
    If sLen > 0 Then
    tString = Left(rString, sLen - 1)
    tString = rString
    End If
    On Error GoTo 0
    ReturnUserName = UCase(Trim(tString))
    End Function

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim wsh As Worksheet
    For Each wsh In Worksheets
    wsh.PageSetup.CenterFooter = Worksheets("Sheet1").Range("A1")
    Next wsh
    End Sub

    In Cell A1 place =ReturnUserName() and the username will appear the second piece of code by Hans in <post:=667,296>post 667,296</post:> places that information into the footer.

    Is this what you are looking for?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Dynamic Footers (MS Excel 2003)

    Welcome to Woody's Lounge!

    To create a dynamic footer, you need a bit of VBA code.
    - Press Alt+F11 to activate the Visual Basic Editor.
    - Double click the ThisWorkbook item under your workbook in the project explorer (the Explorer-like treeview on the left hand side).
    - Enter or copy/paste the following code into the module window that appears:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ThisWorkbook.Sheets(1).PageSetup.CenterFooter = Environ("username")
    End Sub

    This will set the center footer of the first sheet to the login name of the user who has opened the workbook. If you want to do this for all sheets:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim wsh As Worksheet
    For Each wsh In ThisWorkbook.Worksheets
    wsh.PageSetup.CenterFooter = Environ("username")
    Next wsh
    End Sub

    You can modify the code to suit your needs (for example use LeftFooter or RightFooter instead of CenterFooter).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts