Link to home
Create AccountLog in
Avatar of JohnRobinAllen
JohnRobinAllenFlag for Canada

asked on

Display a Progress Bar while VBA code is running in another Word module

Using VBA in a Word document, I have a procedure that takes a bit of time to complete, anywhere from fifteen seconds to three minutes. I want to let the user know the program is working. Messages on the Word Status bar simply do not display, even if I have turned on Screen Updating.
     If that won’t work I would like to display a progress bar in a UserForm. I have some code from http://oreilly.com/pub/h/2607 to do just that. The code for that progress bar apparently wants me to put my running code inside the UserForm that holds the progress bar. That gives problems. I can sort of transfer the variables I need in that location by declaring them as global, but the big problem is that my code calls functions defined in the calling program. I tried putting the functions in the UserForm but it will not recognize them.
     How can I get my code to work and still display the progress bar? Can I get the UserForm to call functions in the calling module? Can I run the code in the calling module and still display the status bar in the UserForm? Is there some other way to display a status bar besides the method described in the Oreilly link above?
     I’m tempted to give up and say it cannot be done unless I can get ALL the code that is doing the work inside the UserForm.
     Thanks for any help anyone can suggest.
     JRA in Priddis, Alberta
ASKER CERTIFIED SOLUTION
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of JohnRobinAllen

ASKER

The seeds of the answer are there. It took an hour or so of fiddling to find out that I do not need to put any code in the UserForm1, and the specifics of the various elements of the form can be set in the source code.
     If any other person reading this would like to see the code that worked for me (it paints a progress bar in addition to printing a message), I'll post it here upon request.

     All that makes life much simpler for me, so thanks again to Graham.
     John Robin
Hi John - I would like to see the code that worked...

Thanks in advance, Steve
Thanks for your interest Steve!

I work with a UserForm1 described in this hack:
http://oreilly.com/pub/h/2607

That gives you the form with the progress bar. I have no code inside the UserForm1.
Let’s say I want the progress bar to display progress as a loop goes through “NumberOfThingsToDo”. I declare that variable as an integer at the start of the procedure that the progress bar will measure. I also declare in the same place two other values used by the progress bar:

Dim NumberOfThingsToDo As Integer
Dim sProgressBarIncrement As Single
Dim lMaxProgressBarWidth As Long

Then just above where I start the loop the bar tracks I put this code:

            '     Initial values of the progress bar that we show in UserForm1
            UserForm1.Label1.Width = 0
            lMaxProgressBarWidth = 200
            sProgressBarIncrement = lMaxProgressBarWidth / NumberOfThingsToDo
            UserForm1.Show vbModeless

            '     Then I start the loop:
            For i = 1 To NumberOfThingsToDo

At the bottom of the loop, just above the “Next i” I have this code:
           '     Update the Progress bar currently displayed: lengthen the bar & show a message
                 UserForm1.Label1.Width = Format(UserForm1.Label1.Width + _
                                 sProgressBarIncrement, "#.##")
                  UserForm1.Caption = "Working on " & i & " of " _
                                 CStr(NumberOfThingsToDo) & " items done."
                  UserForm1.Repaint
                  DoEvents
            Next i

When I finish the loop, I unload the progress bar:
           Unload UserForm1

(End of code for progress bar)
I hope this helps you. It works like a charm for me, but I may have forgotten some other code in the properties of the UserForm1 and its label. If the code does not work for you, please let me know so I can correct and add to the above.

Warm regards,
John Robin (Allen) in Priddis, Alberta (Canada)
Hi John - thanks for posting!  

Hopefully I'll be looking at this in the next few days and, per your request and in the interests of helping others, will let you know if any parts don't work for me.

Cheers,
Steve