I have a workbook that takes about 2-4 minutes to load data from a server and I'd like to implement a userform progress bar. I have found numerous great websites on how to create these progress bars. I've created one that works when I step through the program in debug mode with breaks before the bar size increase, but when I run it without adding the debugging breaks the progress bar begins to update but it eventually bogs down and no more screen updating occurs anywhere on the workbook until it is finished loading.
I have turned ON screenupdating as the first line when the workbook loads. I have Do Events after each progressbar width increase. I open the progress bar form as modeless. But it still seems the workbook gets overclocked with the data processing and loses focus on the workbook and kind of puts it to sleep (no screen updating, mouse pointer in hourglass mode).
Before I post some code I am wondering if there was any quick command I could add to slow down the processing of the data, or maybe cause the progress bar user form to wait for a second or two to let it 'catch up'?
Thanks!
This usually work for me (note the .Repaint):
Public Sub updateProgressBar(ByVal whatPercent As Double, ByVal slowIt As Integer)
Dim outputPercentage As Integer, newWidth As Integer
If whatPercent > 1 Then whatPercent = 1
outputPercentage = CInt(whatPercent * 100)
newWidth = CInt(whatPercent * maxWidthBar)
With ProgressBar
.LabelProgress.Width = newWidth: .LabelProgress.Caption = outputPercentage & "%": .Repaint
End With
delay slowIt
End Sub
'To create a delay
Public Sub delay(ByVal sec As Integer)
Dim strT As String, strSecsDelay As String
strT = Mid(CStr(100 + sec), 2, 2)
strSecsDelay = "00:00:" & strT
Application.Wait (Now + TimeValue(strSecsDelay))
End Sub