How to interrupt VBA execution through the ribbon UI?

WernerGg

WernerGg

New Member
Joined
Oct 19, 2010
Messages
43
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 -->
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
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.
 
Upvote 0
For 2007, you can use OnTime to run the actual code. This will allow cancelling of the running routine (since it allows the callback to finish).
 
Upvote 0
xenou said:
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.
Click to expand...

Great! This is exactly what should happen. But it does not happen under XL2007. There the DoCancel-code is not executed before DoWork has ended.

Good to know, that it will work under 2010 at least.
 
Upvote 0
rorya said:
For 2007, you can use OnTime to run the actual code. This will allow cancelling of the running routine (since it allows the callback to finish).
Click to expand...

Well, of course. But this is not the problem. My DoWork is just a realistic simulation for a long running action which shall be interruptable by the user (Complex statistical curve fitting in my case).

Application.onTime would be a different processing. Unlikely that a real work routine would act like that.
 
Upvote 0
I don't see your point? You simply use OnTime to run exactly the same code as you are running now. There is no difference to the processing code itself, merely the way it is started.
 
Upvote 0
rorya said:
I don't see your point? You simply use OnTime to run exactly the same code as you are running now. There is no difference to the processing code itself, merely the way it is started.
Click to expand...

Application.onTime just schedules another routine for a later time Tx. Then terminates immediatly. The caller continues and will normaly end soon after. My DoCancel would then execute of course. Then nothing happens anymore until Tx.
 
Upvote 0
Did you try it? If you use Ontime, you can cancel the routine using the other button. I thought that was the point of the question? (If not, what was?)
 
Upvote 0
rorya said:
Did you try it? If you use Ontime, you can cancel the routine using the other button. I thought that was the point of the question? (If not, what was?)
Click to expand...

Yes, I tried it. It works. But it is not my problem.

Can you write for me the code that uses onTime and does something like:

Code: 
Sub test_work()
    Dim i As Long, x As Double
    Debug.Print "Start"
    CancelWork = False: IAmWorking = True
    For i = 1 To 20000 '00000
       If CancelWork Then Exit For
       x = Sin(i / 100#)
       Debug.Print i, x
       DoEvents
    Next i
    Debug.Print "Done"
    IAmWorking = False
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA Ribbon Toggle Button
Replies
0
Views
5K
decadence
D
V
  • Question Question
Macro execution: Start from VBA Editor(F5) vs. Start from Custom ribbon
Replies
0
Views
529
Vinci2504
V
dreen
  • Question Question
How can I select a Specific Cell and execute a function in another cell if it is found
Replies
0
Views
610
dreen
dreen
Jerry Sullivan
  • Question Question
Adding Custom Ribbon to a Workbook using VBA
Replies
8
Views
17K
RoryA
RoryA
E
  • Question Question
Use VBA to change the xml previously created by Custom UI Editor to customize the ribbon
Replies
1
Views
3K
Jon Peltier
J

Forum statistics

Threads
1,223,958
Messages
6,175,633
Members
452,661
Latest member
Nonhle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top