McGimpsey & Associates 

Alternate time and date stamps

Alternate 1 (stamp when cleared)

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                With .Offset(0, 1)
                    .NumberFormat = "dd mmm yyyy hh:mm:ss"
                    .Value = Now
                End With
                Application.EnableEvents = True
            End If
        End With
    End Sub

Alternate 2 (date only)

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                With .Offset(0, 1)
                    .NumberFormat = "dd mmm yyyy"
                    .Value = Date
                End With
                Application.EnableEvents = True
            End If
        End With
    End Sub

Alternate 3 (time only)

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                With .Offset(0, 1)
                    .NumberFormat = "hh:mm:ss"
                    .Value = Time
                End With
                Application.EnableEvents = True
            End If
        End With
    End Sub

This page last updated Thursday, 15 January 2004