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

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" <(E-Mail Removed)> wrote in message
news:B3910EB7-948E-48EB-B65C-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:B3910EB7-948E-48EB-B65C-(E-Mail Removed)...
> > 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:11668F82-3E67-4C57-AA72-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:B3910EB7-948E-48EB-B65C-(E-Mail Removed)...
>> > 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
>>
>>
>>