CODEWITHSUNDEEP
vbaexcel
Artur
Artur

Reputation: 343

Cannot interrupt running operation in vba when command button is being pressed

I researched net, but I haven`t found a solution yet and I am still grappling with the following problem:

In vba UserForm I have two command buttons:

When I press 'Run Operation' button I cannot press neither 'Cancel' nor 'x' to stop the running operation and I have to wait until the operation finishes, thus the userform is freezed for around 30 minutes.

enter image description here

Code looks more or less like this:

private Sub Cancel_Click()
   Cancel = True
End Sub

private Sub RunOperation_Click()
   RunOperation.Enabled = False
   Call Macro()
End Sub

private Sub Macro()
   For i = 1 to 100
   'do stuff here
      If Cancel = True Then
      RunOperation.Enabled = True
      Exit Sub
   Exit If
   Next i
End Sub

What`s more both buttons have TakeFocusOnClick set to False.

I`d be grateful for any ideas.

Thanks in advance !

Upvotes: 0

Views: 1027

Answers (1)

Aiken
Aiken

Reputation: 2658

The DoEvents method is your friend here.

What's happening is that since VBA is single-threaded (i.e. only one macro can be running at a time) it's not possible for events (in your case Cancel_Click()) to trigger. The DoEvents method essentially pauses the code wherever it appears to see if any other events have been triggered and resolves them before code execution is resumed.

Try this, it should work:

Private Sub Macro()
    For i = 1 To 100
       'do stuff here
       DoEvents '<~~ Insert this line here
       If Cancel = True Then
           RunOperation.Enabled = True
           Exit Sub
       End If
    Next i
End Sub

Upvotes: 2

Related Questions