Thread: [Solved] ** Using Functions
View Single Post
 
Old 02-06-2011, 03:54 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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...
Reply With Quote