Detecting Table Change

[Steve Haack]

All,
I am using Excel 2007. I have a range on a sheet that I have defined as a
Table using the "Format As Table" button on the Styles tab of the ribbon.

Using Code, I would like to detect when someone has inserted a new row into
the table, so that I can go off and do some other things.

Can anyone tell me how to detect that insertion?

Thanks,
Steve

 

[Jon Peltier]

I didn't just run off and test this, but I would think Worksheet_Change
would cover it. If any formulas reference a table column,
Worksheet_Calculate will also do the trick.

- Jon

 

[Steve Haack]

But, Wouldn't that tell me when ANYTHING on the sheet changes? only want to
know when the table has changed. How would I filter down to that?

Steve

 

[Jon Peltier]

You check for which cells changed. The range that triggers the event is
called Target. A table is called a listobject. This is the basic code you
would use:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.ListObjects(1).DataBodyRange) Is Nothing Then
MsgBox "changed the table"
End If
End Sub

- Jon