Interrupting a Macro in progress

[rayza]

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.

 

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