Get Username - but only once

**Gunther Maplethorpe** · 07-29-2011, 11:39 AM

So, I've cribbed some VBA code to pull the login name of the user into a workbook. (I readily admit that I copied this from another site, as I really have no clue when it comes to VBA.)

This code works a treat for getting the username, and I simply add the formula "=windowsusername()" into whatever cell I want to have it populate.

However, if a different user opens the worksheet, it then changes the result. (That is, if "aliceanders" opens the doc first, then saves it and send it to "bobbrewer", when Bob opens it, the user will be "bobbrewer".)

Question: how can I get this to only update the cell the first time someone opens the document?

The VBA I'm using:

Option Explicit
 'api call for obtaining the username
Private Declare Function GetUserName& Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, _
nSize As Long)
 
Public Function WindowsUserName() As String
     '   ---------------------------------------------
     '   Function to extract the name:
     '   ---------------------------------------------
    Dim szBuffer As String * 100
    Dim lBufferLen As Long
     
    lBufferLen = 100
     
    If CBool(GetUserName(szBuffer, lBufferLen)) Then
         
        WindowsUserName = Left$(szBuffer, lBufferLen - 1)
         
    Else
         
        WindowsUserName = CStr(Empty)
         
    End If
     
End Function

**shg** · 07-29-2011, 12:02 PM

What does "the first time someone opens the document" mean?

The person that created it is the first person to have it open.

**Gunther Maplethorpe** · 07-29-2011, 12:10 PM

I should have explained that the worksheet I'm creating is to be used as a template for others. So, yeah...I'll be the first one opening it, and it will default to my username, then. Is there a way to save it in a 'blank' state, perhaps, and then have it update, once only, the next time it is opened?

Or, perhaps, some way of stating "the next time this particular cell is modified, lock it down"?

**shg** · 07-29-2011, 12:25 PM

Maybe (untested)

Private Sub Workbook_Open()
    With Worksheets("Sheet1").Range("A1")
        If Me.Name Like "*.xlt*" Then
            .Value = vbNullString
        ElseIf .Value = vbNullString Then
            .Value = Environ("username")
        End If
    End With
End Sub

**Gunther Maplethorpe** · 07-29-2011, 12:35 PM

Shoot...I need to get in the practice of stating all my restrictions on these, too. Because the ultimate template will actually be launched from another program that gets a little finicky, I am restricted from using anything that activates upon open of the worksheet, as it appears this one does. I'm still testing whether "on activate" of a particular worksheet will work, but I doubt it will. (I've heard that "on modification" of a certain cell is possible, but I can't find the way to write that, and I'm not certain it would work in this instance anyway; I understand that requires an active modification of a cell, which this would not be.)