** Using Functions

macropod · #1 02-05-2011, 01:32 AM

Hi Rey,

You could change:

Code:

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

to:

Code:

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

Colin Legg · #2 02-06-2011, 03:54 AM

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.

Code:

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...

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Drop Down List and Functions (Linking)	sten	Excel	0	03-17-2010 03:03 PM
Auto-updating basic math functions & cross-referencing tables	FranklinBluth	Word Tables	13	11-19-2009 10:26 AM
Automated Functions??	nickypatterson	Outlook	0	08-27-2009 01:50 PM
Merging a Word doc with VB functions contained	adamwbrown	Word	0	08-13-2008 06:10 AM
How to show help for addin functions?	Movses Asatryan	Excel	0	03-02-2006 05:29 AM