VBA that runs Sub after Data Refresh All completes

[KatiL]

I am a VBA hack and not formally trained.. so apologies if I am confusing or unclear.

I need to create VBA code that notices when Data Refresh All completes. Once complete, it would run a VBA sub from Module1 that I have already created and tested. When complete, I would like a message box to indicate that "Data Refresh All successfully completed. All Import Tabs have been updated to include only refreshed data."

From reading through the responses in other threads I think I need to add some code to a Class Module and to ThisWorkbook. I have included a snip of what I think MIGHT be the correct Class Module code.. but I couldn't wrap my head around what would go in ThisWorkbook... and I am not sure if the Class Module code is correct since I cannot test it until the rest of the code is written.

In case you need to know, the Power Queries that would run are:

TSDATA​

eCMS_TCI_DATA​

BILLING_DATA​

CLEARION_DATA​

TS_W_ERRORS​

The VBA Sub I need to run is called

ALL_IMPORTS_CLEAR_AND_AUTOFILL_FORMULAS​

 

[KatiL]

I restarted Excel and it worked! Sorry I didn't try that sooner... Thanks for all your help!

 

[John_w]

I'm glad you got it working.

Still no MsgBox.

I added the above code and ran it. When processing stopped, I hit F5 which popped up a "Go To" box with each Query Table listed. Nice to be able to see all my queries in a list and navigate to them, but that's not what I need at this time. I am not sure why adding a Stop would make the MsgBox work and I do not want users to have to take another step to reset tables.

You were pressing F5 in the wrong context - the Excel UI - there. To clarify, the Stop line in the code was for debugging purposes only, to see if that part of the code was being executed. When the Stop is reached, the running code is interrupted in the VBA editor and you can press F5 to continue the execution.