Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mouse over event | Excel Discussion (Misc queries) | |||
Wierd event | Excel Discussion (Misc queries) | |||
How to Track Column Insert/Delete Event in Excel | New Users to Excel | |||
Change Font Color Based on Classification | Excel Discussion (Misc queries) | |||
Change of Row event | Excel Discussion (Misc queries) |