View Full Version : Stable progress bar

06-22-2011, 10:06 PM
I have VBA code that imports external data to workbook and saves the workbook multiple times during the process. Since it is a lengthy process, I use a progress bar to show the progress of the process to user.
I have a separate userform that has the progress bar etc. stuff but the problem is that the form sometimes disappears (when the workbook is saved), and the user cannot move it because the form is not responding.
I already use the routine to bring the form to the front of other modeless forms but it still disappeares multiple times during the process.

Is there any way to make the progress bar stable even on saving the workbook, something like Windows has when multiple files are copied from one directory to another.

Kenneth Hobs
06-23-2011, 09:03 AM
I guess that Application.ScreenUpdating = False did not help.

Some API methods might solve the problem but it would be best to have a small example that shows the problem for testing purposes.

06-27-2011, 04:55 AM
ScreenUpdating=False is not helping here. I already use API's here:

Dim mlhWnd As Long
Private Declare Function GetForegroundWindow Lib "user32" () As Long
Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, ByRef lpdwProcessId As Long) As Long
Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long

Private Sub UserForm_initialize()
On Error Resume Next
mlhWnd = FindWindow(vbNullString, Me.Caption)
End Sub

Private Sub TakeFormOnTop()

Dim lFocusThread As Long
Dim lThisThread As Long

lFocusThread = GetWindowThreadProcessId(GetForegroundWindow(), 0)
lThisThread = GetWindowThreadProcessId(mlhWnd, 0)

If lFocusThread = lThisThread Then
SetForegroundWindow mlhWnd
End If

End Sub

I run TakeFormOnTop procedure every time the progress bar width has to be updated (i.e. when the process moves one step forward). I also use Me.Repaint to refresh the form.