How to interrupt VBA execution through the ribbon UI?

[WernerGg]

I have a little Excel 2007 application with a standard ribbon UI. See the CustomUI.xml and the VBA-code below.

I have uploaded an Excel 2007 TestRibbonUI.xlsm to box.net from where you can get it with the link http://www.box.net/shared/8uznug7s3r

My new tab with name "My Tab" and id="tabCustom" has a group "grpCancel" with two buttons "btnWork" and "btnCancel". They shall be enabled /disabled at runtime, but this is not the issue here. See my other topic http://www.mrexcel.com/forum/showthread.php?t=518628 for that. Here both buttons stay always enabled.

Button "btnCancel" shall interrupt the processing startet from button "btnWork", but unfortunately Excel gives the ribbon UI no chance to process the button action "DoCancel" issued from button "btnCancel" as long as the btnWork-action "DoWork" has not finished.

Not even DoEvents or other calls like Application.Screenupdating interrupt DoWork sufficiently to handle the "btnCancel"-action. Note that when doing the same thing from a classical Cancel-Button on the spreadsheet or a form, there is no problem.

Does anybody know how to interrupt DoWork or execute DoCancel in another thread?

I am afraid that new ribbon UI has a very weak integration with the VBA architecture.

Sorry I am not able to enter the xml-code and not the VBA code within code-tags. The vBulletin software always corrupts the things. Stupid! Please download the workbook from the above link.<!-- / message --><!-- sig -->

 

[RoryA]

Application.Ontime Now(), "test_work"

 

[WernerGg]

When I hit your Work button I see the status bar increment 1...2...3...4...
Then I hit the cancel button and the status bar stops incrementing immediately.

Is this not what happens on your end?

I am using XL2010 with Windows XP Pro SP3 on a 32 bit system, with two cores enabled.

Funny. I just wrote that last msg to rorya. I tried that test_work code before. And I CAN interrupt it through the ribbon btnCancel.

But only if I start test_work directly within the IDE. Not if I start it from the ribbon btnWork.

Obviously in XL2007 the ribbon itself and the ribbon-onAction routines run within the same thread. Crazy!

 

[WernerGg]

Application.Ontime Now(), "test_work"

grrrr

But you helped me anyway. See my last msg to xenou

 

[RoryA]

What's the grr for? You put that in the button callback. It then means that you can use the other button (because the first button's callback code has finished) to cancel the code, which you couldn't before. I guess I'm still missing what the problem is.

 

[WernerGg]

What's the grr for? You put that in the button callback. It then means that you can use the other button (because the first button's callback code has finished) to cancel the code, which you couldn't before. I guess I'm still missing what the problem is.

Ahh, now I understand and I have to appologize for that little stupid "grr".

I was always thinking you talk about replacing my "do while Timer < Endtime" workloop by an onTime-construction.

You are right: Starting DoWork through an intermediate onTimer should free the ribbon thread. I will try that today. The ribbon control parameter cannot be passed directly then, but this is not a problem.

I´ll come back later

 

[WernerGg]

Rorya has solved my issue. Thanks a lot.

The solution is to go through a little intermediate routine DoWork called by the ribbon. This routine starts the real workhorse by 'Application.onTime Now(), "DoTheWork"'. DoTheWork can then ask for the global CancelWork variable set through the btnCancel.

Note that if DoTheWork needs some dynamic behaviour like in my case (enable the btnCancel, disable the btnWork) the corresponding guiRibbon.Invalidate has to be done in DoWork as well, not in DoTheWork.

The final code example TestRibbonUI_2.xlsm is uploaded to http://www.box.net/shared/2cysjpu5d5

 

[WernerGg]

This problem is solved. Thanks to rorya!

I have uploaded TestRibbonUI_3.xslm to Box.net http://www.box.net/shared/8omsyxrkmj. Sheet InterruptUI explains the problem and the solution.

The same workbook also shows the solution to "How to preserve or regain the Id of my custom ribbon UI"
(http://www.mrexcel.com/forum/showthread.php?t=518629). See sheet IRibbonUI.

Basically we do:

[SIZE=2][FONT=Consolas][COLOR=black][COLOR=black][FONT=Consolas]btnWork -> [/FONT][/COLOR][/COLOR][/FONT][/SIZE]
 
[SIZE=2][FONT=Consolas][COLOR=black][COLOR=black][FONT=Consolas]Sub DoWork(control As IRibbonControl)[/FONT][/COLOR][/COLOR][/FONT][/SIZE]
[COLOR=black][FONT=Consolas][SIZE=2][COLOR=black][B]  Application.onTime Now(), "DoTheWork"[/B][/COLOR][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Consolas][SIZE=2][COLOR=black]End Sub[/COLOR][/SIZE][/FONT][/COLOR]
 
[COLOR=black][B][FONT=Consolas]Sub DoTheWork()[/FONT][/B][/COLOR]
[FONT=Consolas]' The workhorse of the appl[/FONT]
[FONT=Consolas][SIZE=2][COLOR=black]  ...[/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][COLOR=black]  If CancelWork then ...[/COLOR][/SIZE][/FONT]
[SIZE=2][FONT=Consolas][COLOR=black]  ...[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black]End Sub[/COLOR][/FONT][/SIZE]