VBA Userform Progress Bar

[alpeshpatel09]

Hi

Is there anyway to add a progress bar that mimics the seconds of a clock so for example if the time was 12:00:06 the progress bar would be at 10% complete and once complete (12:01:00) the progress bar will back to 0%
(I already have a clock on the form)

Thank you.

 

[GWteB]

Put two labels on your userform, one with a backcolor other than your userform (LabelOne) and on top of that one a label with a border and a transparant backstyle (LabelTwo).
See whether this meets your needs:

    Me.LabelOne.Width = Me.LabelTwo.Width * VBA.Second(VBA.Time) / 60

 

[alpeshpatel09]

Hi @GWteB

Thank you for the answer, really appreciate it. I have added it to the user form under UserForm_Activate and UserForm_Initialize however it does not refresh.

 

[GWteB]

Hi @alpeshpatel09, because you wrote the following:

(I already have a clock on the form)

my assumption was you meant a dynamic clock, one that refreshes automatically. Apparently that's not the case.
Regarding a progress bar in general, you might find this thread interesting.
A dynamic clock on a userform can be accomplished with code like below.

Workbook Example on DropBox

This goes in the userform's code-behind module:

Option Explicit

Private Type TLocals
    IsCancelled As Boolean
End Type
Private this As TLocals

Private Sub OnCancel()
    this.IsCancelled = True
    Me.Hide
End Sub

Private Sub RunningClock()
    Do While Not this.IsCancelled
        Me.Label1.Caption = VBA.Time$
        Me.Label2.Width = Me.Frame1.Width * VBA.Second(VBA.Time) / 60       ' <<< suggestion of my previous post
        VBA.DoEvents
    Loop
End Sub

Private Sub UserForm_Activate()
    RunningClock
End Sub

Private Sub UserForm_Initialize()
    With Me.Label2
        .BackColor = vbGreen
        .Height = Me.Frame1.Height + 4
        .Top = -2
        .Left = 0
    End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VBA.vbFormControlMenu Then
        Cancel = True
        OnCancel
    End If
End Sub