Interrupting a Macro in progress

rayza

New Member
Joined
Sep 28, 2002
Messages
1
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.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
L

Legacy 98055

Guest
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.
<pre>
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 FLAGS = SWP_NOMOVE Or SWP_NOSIZE
Public Const HWND_TOPMOST = -1
Public Const HWND_NOTOPMOST = -2

Public KillRun As Boolean
Public Excel_hwnd As Long


Sub Test()
'create temp commandbar
MakeBar
'get Excel WindowHandle
Excel_hwnd = GetForegroundWindow()
'set Excel as the topmost window
Call SetWindowPos(Excel_hwnd, HWND_TOPMOST, 0, 0, 0, _
0, FLAGS)
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
Do
DoEvents
If KillRun Then
'deletes the temp commandbar
Application.CommandBars("Cancel").Delete
'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
Loop
End Sub

'will create a temporary commandbar with one button called "Cancel"
Sub MakeBar()
On Error Resume Next
With Application
.CommandBars("Cancel").Delete
.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

</pre>
Tom
 

Watch MrExcel Video

Forum statistics

Threads
1,127,973
Messages
5,627,942
Members
416,282
Latest member
fchagas97

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
Top