Consulting

Results 1 to 3 of 3

Thread: Stable progress bar

  1. #1

    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.

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,911
    Location
    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.

  3. #3
    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
  •