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

Question

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:

1st one ('Run Operation') runs an operation that could take around 30 minutes.
2nd one ('Cancel') was created to interrupt the operation that was triggered off by 'Run Operation'

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 !

Where it says 'do stuff here, insert the command DoEvents (all one word). — user4039065
– user4039065, Commented Nov 26, 2014 at 9:26

Aiken · Accepted Answer · 2014-11-26 09:29:15Z

2

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

answered Nov 26, 2014 at 9:29

Aiken

2,6682 gold badges20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related