lundi 20 avril 2015

Print Date and time in fixed column instead offset for Log of events

Gentlemen, I´d like to create a log of events. With a form I´ll fill cells on the same row starting in column C and I´d like date and time printed automatically in columns A and B. Sometimes information might start from column D and C would be empty. The code below does that through an offset from column C and C only. Even if I expand the range for Intersect, the date and time will be carried out and not stay fixed in A and B. Does anyone would have a hint for me?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim rChange As Range

    On Error GoTo ErrHandler
    Set rChange = Intersect(Target, Range("C:C"))
    If Not rChange Is Nothing Then
        Application.EnableEvents = False
        For Each rCell In rChange
            If rCell > "" Then
                With rCell.Offset(0, -2)
                    .Value = Date
                    .NumberFormat = "[$-2C09]ddd, DD/MM/YYYY"
                    .HorizontalAlignment = xlLeft
                    .EntireColumn.AutoFit
                End With
                With rCell.Offset(0, -1)
                    .Value = Time
                    .NumberFormat = "hh:mm"
                    .HorizontalAlignment = xlCenter
                    .EntireColumn.AutoFit
                End With
            Else
                rCell.Offset(0, -1).Clear
                rCell.Offset(0, -2).Clear
            End If
        Next
    End If

ExitHandler:
    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

Aucun commentaire:

Enregistrer un commentaire