** Using Functions

[djreyrey]

Hello all! Thank you for stopping by.



I'm using the following functions in Excel 07 to retrieve the date and user name of the person who entered a new row. However, when I open the spreadsheet, the existing rows with user names update to my user name. Also, instead of the date showing when the row was entered, it updates to today's date. I don't want the information to change everytime I open the spreadsheet. It should reflect the user name who entered the new row. Here are the functions I'm using:

Declare Function apiGetUserName Lib "advapi32.dll" Alias _
                 "GetUserNameA" (ByVal lpBuffer As String, _
                 nSize As Long) As Long
 
Function GetUserName() As String
    Application.Volatile
    Dim sBuff As String * 25
    Dim lBuffLen As Long
 
    lBuffLen = 25
    apiGetUserName sBuff, lBuffLen
    GetUserName = Left(sBuff, lBuffLen - 1)
 
End Function
 
Function DateAndTime()
    DateAndTime = Now
End Function

The functions are called by using the following formulas (respectively):

=IF(C199>0, GetUserName(), "")

=IF(C199>0, DateAndTime(), "")

I really appreciate your time!!

Thank you,

Rey

[Colin Legg]

Hi Rey,

Instead of using formulas, you will need to update the relevant cells by using the worksheet's Change event handler. Let us know if you need further direction on how to do this?

[djreyrey]

Yes, please, can you assist me on how to use Excels Change event hander? I really appreciate it.

Do I need to convert the code?

Rey

[Colin Legg]

When the user updates a cell in column C, which corresponding cells should be updated with the username and time? Does this apply to the whole of column C?

[djreyrey]

Hi Colin,

Column A contains the username, Column B contains the date, and Column C and beyond have user input data.

This applies for the entire column. The spreadsheet is used as a request form. Each new row that is entered is a new request. The problem is users forget to enter their name and date so I figured I should automate this.

Thanks again!

Rey

[Colin Legg]

This code goes in the sheet's class module as shown in the attached example.

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
                 "GetUserNameA" (ByVal lpBuffer As String, _
                 nSize As Long) As Long
 
Private Function GetUserName() As String
    Dim sBuff As String * 25
    Dim lBuffLen As Long
 
    lBuffLen = 25
    apiGetUserName sBuff, lBuffLen
    GetUserName = Left(sBuff, lBuffLen - 1)
 
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim lRow As Long
    
    On Error GoTo ErrorHandler
    
    'did the user change something in column C?
    If Not Intersect(Range("C:C"), Target) Is Nothing Then
        
        lRow = Target.Cells(1).Row
        
        Application.EnableEvents = False
        Cells(lRow, 1).Value = GetUserName
        Cells(lRow, 2).Value = VBA.Now
        
    End If

ErrorExit:
    Application.EnableEvents = True
    Exit Sub
    
ErrorHandler:
    
    Resume ErrorExit
End Sub

Hope that helps...

[djreyrey]

Colin,

Wow that worked great! Amazing! Thank you for your time and efforts.

Have a great week!

Rey

[djreyrey]

Hi Colin,

I have another question regarding this function - sometimes the same row will get updated more than once. How can I make this function work so that it'll only grab the user name and date when the row was initially created and not any other time when the row is updated? I'm only interested in knowing who created the row and not who updated it.

I really appreciate your time and knowledge.

Rey

[macropod]

Hi Rey,

You could change:

        Cells(lRow, 1).Value = GetUserName
        Cells(lRow, 2).Value = VBA.Now

to:

        If Cells(lRow, 1).Value <> "" Then
                Cells(lRow, 1).Value = GetUserName
                Cells(lRow, 2).Value = VBA.Now
        End If

[Colin Legg]

Hi Rey,

Similar to Paul's reply, I would do it as shown below. This also assumes that if the user cleared out an entry in column C, the corresponding username and time stamp should also be cleared.

Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim lRow As Long
 
    On Error GoTo ErrorHandler
 
    'did the user change something in column C?
    If Not Intersect(Range("C:C"), Target) Is Nothing Then
 
        lRow = Target.Cells(1).Row
        Application.EnableEvents = False
 
        'did the user clear the cell in column C?
        If IsEmpty(Target.Cells(1)) Then
            Range(Cells(lRow, 1), Cells(lRow, 2)).ClearContents
 
        'only add info if column A is empty
        ElseIf IsEmpty(Cells(lRow, 1)) Then
            Cells(lRow, 1).Value = GetUserName
            Cells(lRow, 2).Value = VBA.Now
        End If
    End If
 
ErrorExit:
    Application.EnableEvents = True
    Exit Sub
 
ErrorHandler:
 
    Resume ErrorExit
End Sub

Hope that helps...

[djreyrey]

Yes, this did help! Thank you both for sharing this with me. It works great.

Rey