-
Stable progress bar
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.
-
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.
-
ScreenUpdating=False is not helping here. I already use API's here:
[vba]
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
Else
DoEvents
End If
End Sub
[/vba]
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules