Faking an animated image in a userform?

[BBrandt]

I've gathered that it's not possible to place an animated GIF like this:

in a userform in Excel. As you might have guessed, I've created a macro that, depending on user inputs, can take a bit of time to run, and I want to display something to let the user know that Excel hasn't locked up. Ideally, this would be a sort of faux status bar like the one above that implies that there is work being done without necessarily actually indicating the amount of work completed (I figure that would be easier to code).

Do you have any recommendations about how to accomplish something like this? Ideally also I'd like to not have to download anything in order to make this work, as the spreadsheet is designed for others to use, so it would be inconvenient if they had to install an add-in or similar to get it to run. Thanks in advance!

 

[fpskidmark]

How do I update the progress bar?

 

[Andrew Poulsom]

Like this:

With UserForm1
    .FrameProgress.Caption = Format(PctDone, "0%")
    .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With

You will need to assign a value to PctDone.

 

[fpskidmark]

Thanks, let me give a shoot!

 

[fpskidmark]

Do I keep my code at the end of the Userform code? Or do I place it somwhere in the middle of the Userform code?

 

[Andrew Poulsom]

The progress UserForm has to call your procedure and your procedure has to update the progress bar at appropriate times.

 

[mikerickson]

Alternatly, you could make your own porogress bar.

Label1.BackColor = RGB(0, 0, 0)
Label1.Height = 20

For i = 1 to Limit
    Rem do something slow
    Label1.Width = 400 * Limit / i
Next i

 

[fpskidmark]

Hi Mikerickson/ Andrew,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I'm not giving up (ha-ha).<o></o>
I was able to slow down userfom1 image to coincide with the time it takes for my macro to run, but I have no idea where to place the “Calls" for my others codes in the "main" code below:<o></o>

Any suggestions?

Thank you,
FP

 Sub Test()
'   The UserForm1_Activate sub calls Main
    UserForm1.LabelProgress.Width = 0
    UserForm1.Show
End Sub
Sub Main()
'   Inserts random numbers on the active worksheet
    Dim Counter As Integer
    Dim RowMax As Integer, ColMax As Integer
    Dim r As Integer, c As Integer
    Dim PctDone As Single
    
    Sheets("Random").Visible = True
    Sheets("Random").Select
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    Cells.Clear
    Application.ScreenUpdating = False
    Counter = 1
    RowMax = 1000
    ColMax = 30
    For ro = 1 To RowMax
    For co = 1 To ColMax
            Cells(ro, co) = Int(Rnd * 1000)
            Counter = Counter + 1
        Next co
        PctDone = Counter / (RowMax * ColMax)
With UserForm1
For iii = 1 To 20000
    Rem do something slow
    .FrameProgress.Caption = Format(PctDone, "0%")
    .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
Next iii
End With
 
Call WAIVES: Call BPK_Collections: Call Loc_Dept_User: Call POS_Collections: Call HB_Sum: Call HB_Col: _
Call Voids: Call Cash_Drawer: Call OverShort: Call Brinks: Call Open_Drawer: Call Small_POS_Collections
        
        DoEvents
  Next ro
    Unload UserForm1
   
End Sub

 

[Andrew Poulsom]

Why don't you update the progress between each call here?

Call WAIVES: Call BPK_Collections: Call Loc_Dept_User: Call POS_Collections: Call HB_Sum: Call HB_Col: _
Call Voids: Call Cash_Drawer: Call OverShort: Call Brinks: Call Open_Drawer: Call Small_POS_Collections

 

[fpskidmark]

How? Like this?

Call WAIVES
For iii = 1 To 20000
    .FrameProgress.Caption = Format(PctDone, "0%")
    .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
Next iii
 
Call BPK_Collections
For iv = 1 To 20000
    .FrameProgress.Caption = Format(PctDone, "0%")
    .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
Next iv
ETC......

 

[Andrew Poulsom]

More like:

    PctDone = 0
    With UserForm1
        Call WAIVES
        PctDone = PctDone + 1 / 12
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        Call BPK_Collections
        PctDone = PctDone + 1 / 12
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        Call Loc_Dept_User
        PctDone = PctDone + 1 / 12
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        Call POS_Collections
        PctDone = PctDone + 1 / 12
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        Call HB_Sum
        PctDone = PctDone + 1 / 12
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        Call HB_Col
        PctDone = PctDone + 1 / 12
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        Call Voids
        PctDone = PctDone + 1 / 12
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        Call Cash_Drawer
        PctDone = PctDone + 1 / 12
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        Call OverShort
        PctDone = PctDone + 1 / 12
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        Call Brinks
        PctDone = PctDone + 1 / 12
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        Call Open_Drawer
        PctDone = PctDone + 1 / 12
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        Call Small_POS_Collections
        PctDone = PctDone + 1 / 12
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
    End With