Excel VBA: executing interrupt code (not by pressing "escape")

Question

I have a Do .. While loop in which two For .. loops are nested; the first For .. loop counts from 1 to X, the second loop counts from X to 1. This is currently repeating itself ad infinitum.

Now I want the user to be able to “interrupt” this endless loop and that the program executes XYZ when the loop is interrupted.

I tried using the toggle button in conjunction with the Do .. While loop, but while the above loop is working, the input is not accepted. The state of the button does not change when pressed if the code starts a cycle.

Any suggestions are welcome.

Answer 1

The key is to include DoEvents in loops. This allows Excel to handle things like clicking a button when running code.

Here is the outline. Assign the ButtonClick macro ButtonClick button. Main will run indefinitely until a button is pressed.

 Option Explicit Dim bBreak As Boolean Sub ButtonClick() bBreak = True End Sub Sub Main() Dim X As Long Dim i As Long bBreak = False Do While True X = 1000 For i = 1 To X If bBreak Then Exit Do End If DoEvents Next For i = X To 1 Step -1 If bBreak Then Exit Do End If DoEvents Next Loop If bBreak Then XYZ End Sub