Status Bar

FoeHunter

Board Regular
Joined
Nov 12, 2008
Messages
236
I have a macro that runs four other macros that reads as follows:

Code:
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?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
Code:
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
 

FoeHunter

Board Regular
Joined
Nov 12, 2008
Messages
236

ADVERTISEMENT

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-com:eek:ffice:eek:ffice" /><o:p> </o:p>
When the macro button is pressed the following code is executed:
Code:
Sub ShowDialog()
    UserForm1.LabelProgress.Width = 0
    UserForm1.Show
End Sub
<o:p> </o:p>
Then when the userform is opened, the following code is run
Code:
Private Sub UserForm_activate()
    Call DAV_UTC_Update
End Sub
<o:p> </o:p>
So far so good, everything seems to be working.
<o:p> </o:p>
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:p> </o:p>
Code:
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I don't see this:

Rich (BB code):
'       The DoEvents statement is responsible for the form updating
        DoEvents

anywhere in your code.
 

FoeHunter

Board Regular
Joined
Nov 12, 2008
Messages
236
Well...crud...that was boneheaded...

/off to figure out how to use the DoEvents
 

Watch MrExcel Video

Forum statistics

Threads
1,122,809
Messages
5,598,202
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top