Status Bar

[FoeHunter]

I have a macro that runs four other macros that reads as follows:

Sub Run_All_Calculation_Macros()
' Runs all macros to update values
    UTC_UMD_Update
    DAV_UMD_Update
    DAV_UTC_Update
    Totals_Update
    Sheets("Quick Copy").Select
    Range("T1").Select
End Sub

Because all of these can take up to 3 or 4 minutes to run (well over 7k cell updates) I want to include some sort of a status bar to show a percent complete so they don’t think Excel froze or the computer quit responding. I didn't want to make this a moster sized post so I didn't include the other 4 macros. Is this possible to do with VBA?

 

[doofusboy]

Dim oldStatusBar As Variant
 
        oldStatusBar = Application.DisplayStatusBar
        Application.DisplayStatusBar = True
        Application.StatusBar = "Please wait while blah is being processed..."
 
       ' call all your other macros here 
 
        ' reset status bar
        Application.StatusBar = False
        Application.DisplayStatusBar = oldStatusBar

 

[Andrew Poulsom]

Look here:

http://spreadsheetpage.com/index.php/blog/progress_bars_the_movie/

 

[FoeHunter]

Thank you for the resources , I'll look into it as soon as I can.

 

[FoeHunter]

I tried to copy the format using the userform, but it doesn’t advance, it just sits at zero. This leads me to think that I don’t have the format set up right.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
When the macro button is pressed the following code is executed:

Sub ShowDialog()
    UserForm1.LabelProgress.Width = 0
    UserForm1.Show
End Sub

<o> </o>
Then when the userform is opened, the following code is run

Private Sub UserForm_activate()
    Call DAV_UTC_Update
End Sub

<o> </o>
So far so good, everything seems to be working.
<o> </o>
Here are the first two columns worth of code for the DAV_UTC_Update macro. Basically the macro counts the number of names in column “A” the inputs the value result for the calculation then goes to the next column. This is repeated 80 times. I think the problem is how the counter is updated, but I'm not sure how to fix it.
<o> </o>

Sub DAV_UTC_Update()
'
' Calculates DAV code totals for UTCs
 
    Dim Counter As Integer
    Dim PctDone As Single
    
    PctDone = Counter / 80
        With UserForm1
            .FrameProgress.Caption = Format(PctDone, "0%")
            .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        End With
 
Sheets("DAV UTC").Select
Application.StatusBar = "Counting and calculating all instances where blah blah blah"
With Range("N2:N" & Cells(Rows.Count, "A").End(xlUp).Row) 'Counts number of entries in A
    .Formula = "=COUNTIFS(UTCs!$A:$A,$A2,UTCs!$B:$B,$B2,UTCs!$G:$G,N$1,UTCs!$D:$D,$C2)"
    .Value = .Value
End With
            Counter = Counter + 1
 
With Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row)
    .Formula = "=COUNTIFS(UTCs!$A:$A,$A2,UTCs!$B:$B,$B2,UTCs!$G:$G,O$1,UTCs!$D:$D,$C2)"
    .Value = .Value
End With
            Counter = Counter + 1

 

[Andrew Poulsom]

I don't see this:

'       The DoEvents statement is responsible for the form updating
        DoEvents

anywhere in your code.

 

[FoeHunter]

Well...crud...that was boneheaded...

/off to figure out how to use the DoEvents