Store Name Of The Calling Procedure In A Variable

[NeedForExcel]

Does any one know how to store name of the calling procedure in a variable?

I have many procedures that make the Pivot Table Change, however I want to access the code of Worksheet_PivotTableUpdate only when a certain Procedure is run.

Can anyone help?

[Keebellah]

I found this link which almost confirms ,my thoughts too
http://www.mrexcel.com/forum/excel-q...lications.html
You would have to access the VBE environment.
My idea would be to add a public variable name ProcedureName and have the first line of code in each procedure set the variable equal to the procedure's name.
Then you can use this value to check on it.
Maybe an idea?

[NeedForExcel]

Hi,

Thank you for the reply.. I had already gone through that link before posting.. Thank you for the time anyways..

[Izandol]

Other procedures may disable events before changing pivot table.

[NeedForExcel]

Ill explain a bit more, maybe it will help..

I have Just 1 Pivot Table, and 10 macros that generate different Pivot Reports.. So Basically all those 10 macros access Worksheet_PivotTableUpdate event.

Now, Consider I have few lines of code in the Worksheet_PivotTableUpdate section. I want Worksheet_PivotTableUpdate to only execute the code for only 1 particular macro of the 10. I tried setting flags, using global variables, but I still run into problems..

[Izandol]

What problem do you have setting flag? Why do you not run code you wish directly from macro and not from event or use withevents variable?

[NeedForExcel]

Actually the problem is, I am using Slicers..

Please Login or Register  to view this content.

My requirement is simple.. When The above code is executed and the Pivot Updates the below code is called. When I execute the Sub WhoElseGotIt I want the red line to be executed, but not when a slicer button updates the Pivot Table. The slicer also triggers the Pivot Update event indirectly, and hence PT.ClearTable clears the table, which spoils the remaining procedure..

Please Login or Register  to view this content.

So In short, I want the PT.ClearTable line to be executed only when the Pivot Table is updated by a Sub and not by the slicer..

[Izandol]

First macro will set variable. Called macro will check variable and cleartable if desired and then will clear variable. Do you have problem with this?

[NeedForExcel]

Maybe I have confused myself and hence confused you.. Let me simplify.

Sub WhoElseGotIt sets up the Pivot Table and calls a procedure AddSlicerForProductWhoElseGotIt which sets up the slicer.

As soon as the Pivot Updates, the below procedure is called.

Please Login or Register  to view this content.

Since, the Sub WhoElseGotItReverse (This sub is called on Pivot Update) has a Line to clear the Pivot Table, it causes the problem.. Because, even on slicer update, the macro gets triggered.. Hope I make sense..

[Izandol]

So:

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

and also:

Please Login or Register  to view this content.

or I did not understand you?

[NeedForExcel]

Thank you for the reply.. That isn't working because, even on slicer click the Pivot Table Updates, and calls the below event

Please Login or Register  to view this content.

Which in turn calls Sub WhoElseGotItReverse(UserArray) which executes the line PT.ClearTable before setting bClearTable = False

But I guess this solves the initial problem

Please Login or Register  to view this content.

But I ran into another problem.. I guess, It can't be done this way it seems after all.. I just wish even Slicers had events associated with them..

[Keebellah]

Maybe there are events to check if the pivot table changes?

[NeedForExcel]

That is exactly the I am using - Worksheet_PivotTableUpdate

[Izandol]

I do not understand this. If first macro will not run bClearTable will be False and clear table code will not run. Please show workbook with problem.