Excel VBA to run a script before Refresh/Refresh All

[Lekster07]

Hi,

I tried searching the WWW for this with no luck. Here's my end goal: Unprotect a worksheet before a refresh when Ribbon > Data > Refresh/Refresh All is clicked.

I have a worksheet with a pivot table that is Protected/Unprotected depending on what user do in the sheet itself. I want to make sure that when the sheet is Protected, the user will still be able to do a Refresh when he goes to Ribbon > Data > Refresh/Refresh All (to refresh the pivot table).

I am thinking of intercepting the Refresh All action and running a script that will unprotect the sheet before it actually refresh but I can't seem to find a way to do this. Is this even possible?

Any help is appreciated. Thanks in advance!

 

[ThatExcelGuyKyle]

Please tell me you found out how to solve this. I am literally trying to figure out how to do the same thing. I need to run a script before the pivot tries to do anything else after the refresh button is clicked.