I need to mask some user operations in a MS Excel pivot
table report. This is required so that based on volume of
data, the application can avoid some user operations.

To achieve this I need to trap certain events from Excel
pivot table. I have listed the events I want to trap on
Excel pivot table.

1. Event when a Page Field is Dragged and Dropped
into Row Area or Column Area of the Pivot Table. ( I have
to disallow this action depending on the other dimension
selection scenario)
2. Event when a Dimension is Dragged and Dropped from
Field List into Row Area or Column Area of the Pivot Table
( I have to disallow this action depending on the other
dimension selection scenario)
3. Event when "Expand All" option was selected for
expanding dimension details ( I need to disallow this
depending on the expected volume )
4. Event when a selection is changed in Page Field /
Row Field / Column Field in the Pivot Table


NOTE : The Pivot Table Updated (Excel 2002) event does
not suit the requirement , because it is triggered after
the change takes place. I need to trap before the action
happens.

Think your out of luck as far as using an event. Events that occur before
an action is performed are named with a Before in the event name. To the
best of my knowledge, none of these are associated with pivot tables.

Perhaps you could have your pivot table on a hidden page and link to its
results on a visible sheet - providing an interface that will allow the user
to perform actions you want to support. Then you could have code to adjust
the pivot table to perform those actions.

--
Regards,
Tom Ogilyv

"Shanmugavel" wrote in message
...
I need to mask some user operations in a MS Excel pivot
table report. This is required so that based on volume of
data, the application can avoid some user operations.

To achieve this I need to trap certain events from Excel
pivot table. I have listed the events I want to trap on
Excel pivot table.

1. Event when a Page Field is Dragged and Dropped
into Row Area or Column Area of the Pivot Table. ( I have
to disallow this action depending on the other dimension
selection scenario)
2. Event when a Dimension is Dragged and Dropped from
Field List into Row Area or Column Area of the Pivot Table
( I have to disallow this action depending on the other
dimension selection scenario)
3. Event when "Expand All" option was selected for
expanding dimension details ( I need to disallow this
depending on the expected volume )
4. Event when a selection is changed in Page Field /
Row Field / Column Field in the Pivot Table


NOTE : The Pivot Table Updated (Excel 2002) event does
not suit the requirement , because it is triggered after
the change takes place. I need to trap before the action
happens.

Hi Shanmugavel,

Thank you for posting in MSDN managed newsgroup!

So far as I know on this issue, if we use the pivottable in the excel worksheet.

We could perform some operations for pivottable after the "Worksheet_PivotTableUpdate" event happen in the worksheet. I'd suggest you should
define one variable to store all the necessary state for your pivottable. Then you should write event handling code in the
"Worksheet_PivotTableUpdate" event for the control of PivotTable. If the dimension is altered, after the comparison with the stored information in
the variable, you can restore it. You can insert one class module in your VBA project so that you can define all the necessary information in this
class and store all the necessary pivottable information in this class object. For example:

'Code beding---------------------------------------------------------

'Please insert one class module in the excel file
'then define the member according to your scenario, this is just one sample

public iRowDimension as Integer
public iColDimension as Integer

public iRowDimension() as string
public iColDimension() as string
...

'you can also define some methods within this class
...

'Code end-----------------------------------------------------------

You may need to code very carefully to check all the necessary information regarding the pivottable.

In anothr way, I'd also suggest you may use the method Tom has suggested. You can create one pivottable in the background. Then in several
user events, for example double click or button click, you can fill some range with the pivottable data for the excel workbook user.

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Shanmugavel,

We can use the object "pivottable" to locate the pivottable of Excel from worksheet.Pivottables([index]). There is no event directly bounded with it
so we can't find any event for pivottable object in the excel online documentation. We should handle this object in the worksheet event.

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.