Interrupting a Macro in progress


New Member
Sep 28, 2002
I have a macro that takes approximately 5 to 10 minutes to run. It is designed to control other applications and gather data from these applications using API functions and changes focus to different applications during the entire process. However, there are times that I may want to stop the Macro in the middle of running. It has become difficult to try to Alt-Tab back to the Excel Spreadsheet and Kill the macro with the Ctrl-Break command, since I only have abut 1/2 second to get the focus to Excel and send the kill command before the focus is changed back to another application.
I have created a userform that contains a message the the macro is in progress and a "Cancel" button on the form. I would like it to remain on top while the macro is in progress and kill if the Cancel button is pressed.
My problem is letting the macro continue it's progress while the form is displayed. I am using Excel from Office 97 which does not include the feature of a Modeless form (I'm not even sure that would help). I guess I would need to use the Excel 6.0 which includes the feature for Multithreading.
Do you have any suggestion on how to accomplish this? I also welcome any alternative methods.

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Legacy 98055

Hi Rayza.
To get around the modal issue, you can use the API or use a custom commandbar. Play around with this code:
DoEvents can cause problems in some situations...

All code placed in one standard module. Creates a temp commandbar with one button labled "&Caption..." Runs an infinite loop until the button is clicked.
Option Explicit
Public Declare Function GetForegroundWindow Lib "user32" () As Long

Declare Function SetWindowPos Lib "user32" _
(ByVal hwnd As Long, _
ByVal hWndInsertAfter As Long, _
ByVal x As Long, _
ByVal y As Long, _
ByVal cx As Long, _
ByVal cy As Long, _
ByVal wFlags As Long) As Long

Public Const SWP_NOMOVE = 2
Public Const SWP_NOSIZE = 1
Public Const HWND_TOPMOST = -1
Public Const HWND_NOTOPMOST = -2

Public KillRun As Boolean
Public Excel_hwnd As Long

Sub Test()
'create temp commandbar
'get Excel WindowHandle
Excel_hwnd = GetForegroundWindow()
'set Excel as the topmost window
Call SetWindowPos(Excel_hwnd, HWND_TOPMOST, 0, 0, 0, _
KillRun = False
'simulate your code running by creating an endless loop
'the DoEvents statement allows other processes to run, such as
'you would need to place this statement in several strategic locations
'within your macro
'the cmdCancel_Click in your UserForm1 class
If KillRun Then
'deletes the temp commandbar
'end Excel's reign as Topmost window
Call SetWindowPos(Excel_hwnd, HWND_NOTOPMOST, 0, 0, _
0, 0, FLAGS)
Exit Sub 'or whatever you want
End If
'visual counter to "see" the code running
Range("A1") = Range("A1") + 1
End Sub

'will create a temporary commandbar with one button called "Cancel"
Sub MakeBar()
On Error Resume Next
With Application
.CommandBars.Add(Name:="Cancel").Visible = True
.CommandBars("Cancel").Controls.Add Type:=msoControlButton
With .CommandBars("Cancel").Controls.Item(1)
.Caption = "&Cancel..."
.OnAction = "StopMacro"
.Style = msoButtonCaption
End With
End With
End Sub

Sub StopMacro()
'sets the flag to stop your procedure
KillRun = True
End Sub

Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

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
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 "".
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