CODEWITHSUNDEEP
vbaexcel
nmergulh
nmergulh

Reputation: 39

Modify vba code for time stamp cell based on row changes to only table range

Here is a a piece of code which time stamps in selected cell when any cell in the row changes. 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Const DateStampColumn As Long = 10    'Date stamp column number
For Each r In Target.Rows
    For Each c In r.Cells
        If Not IsEmpty(c) Then
            Application.EnableEvents = False
            Cells(r.Row, DateStampColumn).Value = Date
            Application.EnableEvents = True
            Exit For
        End If
    Next c, r
End Sub

Is it possible to modify this code so it only applies to the table range I have on the worksheet. My table is called table6 and has a fixed column range from A-P. However the row count will be flexible as new data is added.

Upvotes: 1

Views: 268

Answers (2)

DisplayName
DisplayName

Reputation: 13386

Here is a a piece of code which time stamps in selected cell when any cell in the row changes.

then you can avoid loops

Private Sub Worksheet_Change(ByVal Target As Range)
    Const DateStampColumn As Long = 10    'Date stamp column number
    Dim tblRng As Range

    If WorksheetFunction.CountBlank(Target) = Target.Count Then Exit Sub ' do nothing if cells are being cleared

    Set tblRng = Me.ListObjects("Table6").DataBodyRange
    If Intersect(Target, tblRng) Is Nothing Then Exit Sub ' do nothing if changed cells do not belong to "Table6" table

    Application.EnableEvents = False
    Intersect(tblRng.Columns(DateStampColumn), Target.EntireRow) = Date
    Application.EnableEvents = True
End Sub

Upvotes: 0

SJR
SJR

Reputation: 23081

I just added this code to your previous question.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range, r1 As Range
Const DateStampColumn As Long = 8    'Date stamp column number

Set r1 = Intersect(Target, activeSheet.ListObjects("Table6").DataBodyRange)

If Not r1 Is Nothing Then
    For Each r In r1
        If Not IsEmpty(r) Then
            Application.EnableEvents = False
            Cells(r.Row, DateStampColumn).Value = Date
            Application.EnableEvents = True
            'Exit For
        End If
    Next r
End If

End Sub

Upvotes: 2

Related Questions