PDA

View Full Version : Show userform while macro runs



vinny2984
03-29-2009, 07:41 AM
i have made a userfor which just displays a message saying "slow macro running.........." which i want to keep displayed on the screen while the slow macro runs, and then remove it at the end.

Does anyone know how to keep it showing while the macro runs?

many thanks

GTO
03-29-2009, 08:34 AM
Greetings Vinny,

As with most stuff, I imagine you could do this several ways. A fairly easy way (IMO) is to have the userform call the long running code, using the form's Activate event.

In the attached rude/crude example, you'll note that althought rem'd out, I included the QueryClose, as you'll most likely want to disable, hide, or at least cancel the close button from the userform.

While the example is very cursory, I tacked in a simple pseudo progress bar, a tactic I learned from here in the KB. Sorry, but I don't have a better example handy.

Anyways, this should give you the basic idea and I think you'll find that you can make a pretty nifty UF/progressbar that will keep the user distracted while your code runs.

Hope this helps,

Mark

PS - Forgot to mention, I did not bother with a button... Open VBIDE and get to the userform's code window; press F5

vinny2984
03-29-2009, 09:32 AM
Mark
many thanks for that, I'm playing around with it now to get my code in. thats the easiest code i seen to get a progressbar,excellent.

Although i'm not done yet, it appears as though the progress box will close when the code runs, is there something i need to do stop it disapearing?
thanks.

GTO
03-30-2009, 02:18 AM
Mark
many thanks for that, I'm playing around with it now to get my code in. thats the easiest code i seen to get a progressbar,excellent.

Although i'm not done yet, it appears as though the progress box will close when the code runs, is there something i need to do stop it disapearing?
thanks.

Hey Vinny,

I'm not quite certain what you are referring to in "...the progress box will close when the code runs..."

As I see that you used the term progressbar earlier, I am guessing that you mean the userform?

If this is the case, I am afraid that you have gotten a bit off-track someplace. In the cursory example, I used a simple timed loop to replicate the time it takes for your long code to run.
Sub MyBigMacro()
Dim i As Long

For i = 1 To 11
'// this is ONLY to demo the time taken by the code to run. The pseudo //
'// progressbar's advance could normally be reset throughout the code to show //
'// accurate progress. //

Application.Wait (Now + TimeValue("00:00:01"))

UserForm1.lblIndicator.Width = i * 20
DoEvents
Next
End Sub


So in a copy of your wb, 'MyBigMacro' should be substituted with the main procedure for the long running code you referred to.

Now let's just refer to your main procedure as "MainProc". MainProc is probably currently being called from a button or an event, right? My suggestion is to have the button/event not call MainProc, but rather Show the userform. The userform's Activate then calls MainProc.

Now, as long as MainProc (or "MyBigMacro" in the example) is not completed, the userform will remain displayed:

Userform Code:
Private Sub UserForm_Activate()
Call MyBigMacro
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then Cancel = False
End Sub

So then... in whatever your MainProc is actually called, this is where you would decide on good places to update the width of .lblIndicator. You may find that you have a long running loop someplace that would be a good place, or if the code is more linear in execution, you may find that right after certain "bigger" or slower executing lines (like opening wb's, creating objects and so on) good places to update .lblIndicator. Of course you may determine that the best thing will be a combination of updating in certain loops, as well as increasing .lblIndicator after certain code lines execute.

Using Malcom's KB searcher (Bravo! Cheers! and a LifeSaver award for Malcom BTW), I found the KB Article authored by DRJ (Jacob); here it is:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=169

I was also able to locate a copy of DRJ's example workbook - see attached.

Hope this helps:) ,

Mark

vinny2984
03-30-2009, 07:11 AM
Mark
I've found the problem.

I did what you have described below,i.e. replacing your example code for the userform progress bar with some example code of my own and put in the progress bar updated values throughout my code.
That all worked fine.
But when i tried to make it work with my actual code, the userform disapeared.
The problem I couldn't understand is why the userform disappeared as soon as my code started to run.

The answer to that problem is the actual macro I want to run.
In this instance the code in question, opens a PDF file, copies the data and then pastes it in excel, and then sorts it out.
I think this is the problem. As soon as the PDF file opens, the userform with the progress bar disapears. I don't think there's a way round that, so I'm now playing with it to see if I can refresh the userform after the PDF opens.

So in summary. Your code works fine, but my application of it on this occasion needs some work.

Thanks for sending the link for the other example, I will have a play with that too and se where I end up.

Many thanks for your help.
Vinny