Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Display Excel Form while VBA code runs

Posted on 2012-12-31
3
Medium Priority
?
343 Views
Last Modified: 2012-12-31
I have an excel workbook with a lot of custom code going on in it and it takes a while to calculate (5-7 seconds)

Because of this, instead of leaving calculation set to Automatic, I manage it with VBA as the user clicks around and does different things (toggling calculation on and off, and recalculating programmatically as necessary).  All of this works fine.

As a nice-to-have, I'd like to display a form with a label on it that says "Calculating.  Please wait" while the calc occurs so the user doesn't just think their computer is broken.  Problem is, when I display such a form via code, the rest of the code doesn't complete until I close the form manually again (clicking on the X button).  Is there a way around this?

In other words:  is there a way to make the form run asynchronously from the code that displayed it?

AR
0
Comment
Question by:ducky801
  • 2
3 Comments
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 38732936
Do this.

UserForm1.Show vbModeless
0
 
LVL 5

Author Closing Comment

by:ducky801
ID: 38733546
Works like a charm!  Thanks!
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38733553
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question