Loading Screen Userform; Make X Exit Sub When Clicked

[Ottsel]

I have a macro that is very time consuming, so I created a loading screen that calculates when it'll finish, so the user isn't just staring at a blank screen wondering when it'll be complete.

The issue I'm having is when I click the red X in the right hand corner to cancel the userform my macro continues to run in the background even though the userform or loading screen has been closed.

What can i add to the userform to call off or exit the sub? I'm trying to avoid making a "cancel" button.

 

[Micron]

Depends on what your code does. If it loops you might be able to include a check that the form is open. This might slow it down somewhat. If you can't do that then I don't see any option. VBA is only capable of asynchronous operation (single thread) so in the absence of some sort of conditional check in that code, outside actions can't affect the code that is running.

 

[Jaafar Tribak]

Yes. Like Micron pointed out, use a boolean flag that starts off as FALSE and then set it to TRUE in the userform Terminate or QueryClose events.
Place a check of the boolean variable inside your loop and if it returns TRUE it means the UserForm has closed therefore you can exit the loop.

Something along these lines:
If bFlag then Exit Do

Note, If the loop code is inside the userform module, you should declare the boolean flag variable at the top of the userform module... If the loop code is not in the userform module, you must declare the boolean variable as PUBLIC in a Standard Module.

 

[Ottsel]

Yes. Like Micron pointed out, use a boolean flag that starts off as FALSE and then set it to TRUE in the userform Terminate or QueryClose events.
Place a check of the boolean variable inside your loop and if it returns TRUE it means the UserForm has closed therefore you can exit the loop.

Something along these lines:
If bFlag then Exit Do

Note, If the loop code is inside the userform module, you should declare the boolean flag variable at the top of the userform module... If the loop code is not in the userform module, you must declare the boolean variable as PUBLIC in a Standard Module.

Apologies for the delay in getting back to this; I had the stomach flu over the weekend shortly after this post.

When you mentioned QueryClose Events it lend me to p2p wrox, which that source worked quite well for my situation.
Source

I'm sure I could of just used the boolean within my for loop, but the QueryClose did the trick.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        Sheets("Overview").Range("D12:K1000").ClearContents
        MsgBox "Import cancelled", vbOKOnly + vbCritical
        End
    End If
End Sub

 

[Micron]

You should be able to just check the boolean in the loop as we've suggested. Looks like you decided to call a function in the loop instead. If you're happy with that, then all is well I guess. Hope you're feeling better.

 

[Ottsel]

You should be able to just check the boolean in the loop as we've suggested. Looks like you decided to call a function in the loop instead. If you're happy with that, then all is well I guess. Hope you're feeling better.

Thank you - I am doing better; can't deal with the nausea. I'm just glad it passed.

I did try it, but I believe I kept structuring it incorrectly, but going off that source from p2p it did the trick and works well for this project. I'll do some research later and see if I can come up with a version where I use the boolean instead within the for loop. Perhaps it'll run smoother, but the knowledge of how to structure it correctly will be worth it regardless.

By no means an expert with VBA; just learning one step at a time.