VBA cancel button

I have an Excel macro that works just fine. However, in certain cases, the macros can run for a VERY long time, producing many sheets with 500k rows per sheet.

If I sense that the data is such that the macro will keep running for a very long time, I open the Task Manager and kill the Excel process. This works, but isn't exactly pretty. Question: I know that I could put a Cancel button on the userform, but my fear is that given the processing that is going on, if the button were clicked, this click would not be handled until the processing is done.

Any suggestions?

If you put a DoEvents statement somewhere in the loop, the button can be used to set a flag you check for then exit the processing loop.

Thank you. The code seems to be working, but for some reason the mouse changes from an hourglass to a small plus sign when the DoEvents line is executed...

You can also block all user interactions in the userform by setting its Enabled to False and using GetAsyncKeyState to check if the Esc key is pressed to cancel the operation.

Code:

---

Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
 
 
Private Sub CommandButton2_Click()
Dim i As Long, t As Single
t = Timer
Application.ScreenUpdating = False
Enabled = False
For i = 1 To 500000
 ' random task
 Cells(i, "A").Value = Cells(i, "A").Value + Cells(i, "B").Value
 
 'update status
 If (Timer - t) > 0.1 Then
  Caption = (i * 100 \ 500000) + 1 'displays progress
  DoEvents
  If GetAsyncKeyState(27) < 0 Then
      If MsgBox("Do you want cancel the operation ?", vbYesNo Or vbExclamation) = vbYes Then
        Exit For
      End If
  End If
  t = Timer
 End If
Next
Application.ScreenUpdating = True
Unload Me
End Sub

---