ScreenUpdating and loading bar in excel

Question

I am running a lot of code for creating some reports.

To see how far the creation of reports has proceeded, I want to show a loading bar on my control sheet.

Therefore I use conditional formatting to create a loading bar in a cell. When a report (=a worksheet) is finished with calculating, the loading bar has reached 100% (full).

The problem I have now shows in updating these loading bars.

When running my code, different macros are executed. The loading bars work for 4-5 Macros, after that the screen freezes until all macros are finished. Where is the problem here?

The filling of the progress bar is done by another macro that turns on screenupdating, sets a new progress value on the sheet and turns screenupdating off.

Answer 1

I recently added a Progress Indicator form to one of my files.

I created a UserForm, added a frame, and a label inside that frame, and a couple of other labels that I could update with specifics later.

I load the userform at the start, and pass the % that has completed and what I want the labels to say to this

Public Sub ProgressUpdate(ByVal dCalc As Double, ByVal Process As String)
  With ProgIndi
    If .Visible Then
      .Process.Caption = Process & "..."
      .PercentCompleted.Caption = Int(dCalc * 100) & "% Completed." 
      .Bar.Width = dCalc * 200                                      
      DoEvents                                                      
    End If
  End With
End Sub

At the end of the macro, unload the form. You can leave the ScreenUpdating at False and the form still updates.

Answer 2

Put DoEvents in your loop so that Excel 'catches up' with any actions

Put .Repaint in your loop for your form so that the form itself updates