Event Triggered Change (VBA) with Tables

[TheRogue!]

I'm terrible w/ VBA, so I appologize, in advance.

I have a table (Table1), with 3 columns (ColumnA, ColumnB & ColumnC).

I need to come up w/ some VBA code for a Cell Change Event.

If anyone types anything in ColumnA, I need the cell in ColumnC of the corresponding row to automatically populate with "banana"

If anyone types anything in ColumnB, I need the cell in ColumnC of the corresponding row to automatically populate with "strawberry"

ColumnC should indicate either "banana" or "strawberry" depending upon whether the data, in ColumnA or ColumnB, OF THE SAME ROW were changed last.

I've been trying (which obviously doesn't work):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "Table1[ColumnA]" Then
[Table1[ColumnC]] = "banana"
ElseIf Target.Address = "Table1[ColumnB]" Then
[Table1[ColumnC]] = "strawberry"
End If
End Sub

 

[Jerry Sullivan]

Hi again TheRogue,

Here's one way to do that. This example assumes that Table1 and its three fields exist and does not have any code to validate that or handle exceptions.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count <> 1 Then Exit Sub

    
    If Not Intersect(Target, [Table1[ColumnA]]) Is Nothing Then
        On Error GoTo CleanUp
        Application.EnableEvents = False
        Cells(Target.Row, [Table1[ColumnC]].Column) = "banana"

    ElseIf Not Intersect(Target, [Table1[ColumnB]]) Is Nothing Then
        On Error GoTo CleanUp
        Application.EnableEvents = False
        Cells(Target.Row, [Table1[ColumnC]].Column) = "strawberry"
    End If

CleanUp:
    Application.EnableEvents = True
End Sub

Note that if you are certain that field ColumnC will always be offset two cells to the right of field ColumnA, then you could simplify to...

Target(1,3) = "banana"

...however in a real-world scenario the fields might be "Budget" "Actual" and "Variance". One of the benefits of using a Table's structured references is that it allows you to rearrange the fields without having to modify code written for a specific layout.

 

[TheRogue!]

Thanks Again, Jerry.

Once again, you've saved my bacon.

Despite the fact that I would classify myself as an Intermediate Excel User (I'm pretty good with formulas, macros, arrays); thanks to this forum (& mostly you) my employer & my co-workers think that I'm some sort of genius-level Excel Demi-god.

 

[bnj1776]

Thank you Jerry, you just helped me solve tagging who and when as follows:

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    'Automatically updates any table that has "UpdatedBy" and "UpdatedOn" columns
    
    Dim c                               As Range
    Dim lo                              As ListObject

    Application.EnableEvents = False
    
    For Each c In Target.Cells
        'Debug.Print Now & "  " & Sh.Name, c.Address, """" & c.Value & """"
        For Each lo In Sh.ListObjects
            If Not Intersect(c, lo.DataBodyRange) Is Nothing Then
                On Error Resume Next
                    Cells((c.Row), lo.ListColumns("UpdatedBy").DataBodyRange.Column) = Trim(Environ("Username"))
                    Cells((c.Row), lo.ListColumns("UpdatedOn").DataBodyRange.Column) = Now
                On Error GoTo 0
                Exit For 'lo
            End If
        Next lo
    Next c
    
Exit_Workbook_SheetChange:
    Set lo = Nothing
    Set c = Nothing
                
    Application.EnableEvents = True
    
End Sub

 

[Jerry Sullivan]

Glad that helped.

Consider adding a check before

 For Each c In Target.Cells

...to avoid needing to loop through 1 million+ cells when an entire column is inserted or deleted.

Perhaps get the intersection of Target and any ListObjects ranges then step through each of those cells.

 

[bnj1776]

Excellent suggestion!
Thank you

Here is the new version which skips all but the Target cells that are actually in a table.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)    
    'Automatically updates any table that has "UpdatedBy" and "UpdatedOn" columns
    
    Dim c                               As Range
    Dim rng                             As Range
    Dim lo                              As ListObject
    
    For Each lo In Sh.ListObjects
        Set rng = Intersect(Target, lo.DataBodyRange)
        If Not rng Is Nothing Then
            For Each c In rng.Cells
                Application.EnableEvents = False
                    On Error Resume Next
                        Cells((c.Row), lo.ListColumns("UpdatedBy").DataBodyRange.Column) = Trim(Environ("Username"))
                        Cells((c.Row), lo.ListColumns("UpdatedOn").DataBodyRange.Column) = Now
                    On Error GoTo 0
                Application.EnableEvents = True
            Next c
        End If
    Next lo
    
Exit_Workbook_SheetChange:
    Set c = Nothing
    Set rng = Nothing
    Set lo = Nothing
    
End Sub