Simple Progress Bar

[mmetzinger]

Ok, so I am looking to do a very simple progress bar and need some help with the code. I am still learning VBA and can't quite figure out this one. What I am looking for is something like this:

Sub MyMacro()

Create a progress bar window with the indicator at 0

Run the first part of my macro

After this finishes have it do some kindof simple operation to change the progress bar to 10%

Repeat this process until the macro finishes

end sub

The macro I have is about nine pages worth of code and it really doesn't have any shared events to base the operation off of. That is why I just want ot be able to set a line of code that says like:
Window.progressbar.size = 10 (or something like this)

Any help? O, and I am using Office 2010 on a Win 7 64bit Quad Core machine.

 

[Michael M]

Hi
Have a look at Chip Pearsons site for a download of a Progress Window.

http://www.cpearson.com/excel/Progress.htm

 

[chuckchuckit]

If I remember right this is a generic status bar at the bottom of an Excel page.
Not tested, but try:

Sub StatusBarShow()
    Dim Lcounter As Long
    For Lcounter = 0 To 100000000
    If Lcounter Mod 1000000 = 0 Then
        Application.StatusBar = "Processing Record " & Lcounter
    End If
    Next Lcounter
    Application.StatusBar = False
End Sub

Also on page 289 of the red book called "Excel 2007 VBA Programmers reference" has a section on using a popup type of box with a progress indicator bar.

Also I know you could use a user form box with the vbModeless parameter that will allow a box to remain on the screen that you could update perhaps with a status type bar, while your code runs in the background until you close the box.

Here is some basic raw code for that:

Load UserForm1                    'Or whatever name it has
UserForm1.Show vbModeless         'vbModeless means no user input needed
 
   'Run whatever code here which runs even though box is still displayed
 
Unload UserForm1                  'also closes UserForm1 box

It will take some playing around with the code (F8 stepping through it while trying different things), but the basics for different ones are there.