Detecting Table Change
 

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

Detecting Table Change
 

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Steve Haack" wrote in message
...
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

Detecting Table Change
 

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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Steve Haack" wrote in message
...
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

Detecting Table Change
 

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

"Steve Haack" wrote in message
...
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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Steve Haack" wrote in message
...
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