Insert Row (Event Classification)
 

Hi ...

Using VBA on Worksheet Events I have been able to get two pivot tables on
the same Worksheet to behave exactly like I need them to, EXCEPT for two
Events:

I can't seem to locate the Events that comprise the action 'Insert Row' or
'Insert Column', and I need the VBA code to react to those two events.

Anybody know what Worksheet Events cover 'Insert Row' and 'Insert Column'?

Thanks for your help ...

William 3

Insert Row (Event Classification)
 

The _Change() event fires. Target.Address will be either a column reference
such as $C:$C or a row reference as $2:$2.

It's easy to determine what events fire and when they fire by placing a
simple MsgBox action within them and observing what messages you get as you
make various actions, such as:

Private Sub Worksheet_Activate()
MsgBox "Activate Fired"
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
MsgBox "BeforeDoubleClick Fired at " & Target.Address
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
MsgBox "BeforeRightClick Fired at " & Target.Address
End Sub

Private Sub Worksheet_Calculate()
MsgBox "Calculate Fired"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Change Fired " & Target.Address
End Sub

Private Sub Worksheet_Deactivate()
MsgBox "Deactivate Fired"
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
MsgBox "FollowHyperlink Fired at " & Target.Address
End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
MsgBox "PivotTableUpdate Fired at " & Target.Address
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "SelectionChange Fired at " & Target.Address
End Sub


"WCM" wrote:

Hi ...

Using VBA on Worksheet Events I have been able to get two pivot tables on
the same Worksheet to behave exactly like I need them to, EXCEPT for two
Events:

I can't seem to locate the Events that comprise the action 'Insert Row' or
'Insert Column', and I need the VBA code to react to those two events.

Anybody know what Worksheet Events cover 'Insert Row' and 'Insert Column'?

Thanks for your help ...

William 3

Insert Row (Event Classification)
 

Hi J ...

Thanks for the reply. I finally got it to work by using the
Workbook_SheetChange Event. Problem with that Event is that it is really
sensitive (fires for any change, no matter what), but I was able to condition
it so that it only does stuff when I need it to. Thanks again ...

Bill



"JLatham" wrote:

The _Change() event fires. Target.Address will be either a column reference
such as $C:$C or a row reference as $2:$2.

It's easy to determine what events fire and when they fire by placing a
simple MsgBox action within them and observing what messages you get as you
make various actions, such as:

Private Sub Worksheet_Activate()
MsgBox "Activate Fired"
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
MsgBox "BeforeDoubleClick Fired at " & Target.Address
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
MsgBox "BeforeRightClick Fired at " & Target.Address
End Sub

Private Sub Worksheet_Calculate()
MsgBox "Calculate Fired"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Change Fired " & Target.Address
End Sub

Private Sub Worksheet_Deactivate()
MsgBox "Deactivate Fired"
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
MsgBox "FollowHyperlink Fired at " & Target.Address
End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
MsgBox "PivotTableUpdate Fired at " & Target.Address
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "SelectionChange Fired at " & Target.Address
End Sub


"WCM" wrote:

Hi ...

Using VBA on Worksheet Events I have been able to get two pivot tables on
the same Worksheet to behave exactly like I need them to, EXCEPT for two
Events:

I can't seem to locate the Events that comprise the action 'Insert Row' or
'Insert Column', and I need the VBA code to react to those two events.

Anybody know what Worksheet Events cover 'Insert Row' and 'Insert Column'?

Thanks for your help ...

William 3

Insert Row (Event Classification)
 

You could continue to use the Workbook_SheetChange() event if you're
concerned with changes on several possible sheets. You could use the
individual Worksheet_Change() event if you are only concerned with a very few
sheets in a workbook with many sheets in it - less overhead in terms of
processing time since you wouldn't be testing all cases and just aborting the
processing more often than not.

"WCM" wrote:

Hi J ...

Thanks for the reply. I finally got it to work by using the
Workbook_SheetChange Event. Problem with that Event is that it is really
sensitive (fires for any change, no matter what), but I was able to condition
it so that it only does stuff when I need it to. Thanks again ...

Bill



"JLatham" wrote:

The _Change() event fires. Target.Address will be either a column reference
such as $C:$C or a row reference as $2:$2.

It's easy to determine what events fire and when they fire by placing a
simple MsgBox action within them and observing what messages you get as you
make various actions, such as:

Private Sub Worksheet_Activate()
MsgBox "Activate Fired"
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
MsgBox "BeforeDoubleClick Fired at " & Target.Address
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
MsgBox "BeforeRightClick Fired at " & Target.Address
End Sub

Private Sub Worksheet_Calculate()
MsgBox "Calculate Fired"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Change Fired " & Target.Address
End Sub

Private Sub Worksheet_Deactivate()
MsgBox "Deactivate Fired"
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
MsgBox "FollowHyperlink Fired at " & Target.Address
End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
MsgBox "PivotTableUpdate Fired at " & Target.Address
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "SelectionChange Fired at " & Target.Address
End Sub


"WCM" wrote:

Hi ...

Using VBA on Worksheet Events I have been able to get two pivot tables on
the same Worksheet to behave exactly like I need them to, EXCEPT for two
Events:

I can't seem to locate the Events that comprise the action 'Insert Row' or
'Insert Column', and I need the VBA code to react to those two events.

Anybody know what Worksheet Events cover 'Insert Row' and 'Insert Column'?

Thanks for your help ...

William 3