Excel Userform animated dots on loading

Question

There are a lot of tutorials in the Internet. However I was not able to find anything suitable. Is there any way to make animated dots on loading?

The idea is to make a loop of animated dots ..... on userform so they would appear one after another and then would start over after some amount of dots.

So I input a dot to Label1 and move it to left after certain time criteria?

My current code for UserForm:

    Private Sub UserForm_Initialize()
HideTitleBar.HideTitleBar Me
    Call loadingdots
End Sub

Code for Private Sub Workbook_Open():

Loading.Show (vbModeless)

Dim RngCom As Range
Dim RngTurb As Range
Dim RngGen As Range

Application.Wait (Now + TimeValue("00:00:06"))

ThisWorkbook.Worksheets("MAIN").ScrollArea = "$A$1:$BL$45"
    Application.DisplayFormulaBar = False
    ActiveWindow.DisplayHeadings = False
    ActiveWindow.DisplayGridlines = False
  etc...
  Unload Loading
'Application.ScreenUpdating = True
  End Sub

Answer 1

The most elegant solution would likely to be the OnTime method.

Place a label inside your UF and remove the caption. Next, in a regular module (so not that of the UF), place this subroutine:

'this function ensures the self-activating sub will stop if the UF has been closed
Public Function IsLoaded(form As String) As Boolean
Dim frm As Object
For Each frm In VBA.UserForms
    If frm.Name = form Then
        IsLoaded = True
        Exit Function
    End If
Next frm
IsLoaded = False
End Function


Public Sub loadingdots()

If IsLoaded("UserForm1") = True Then
    If Len(UserForm1.Label1.Caption = 4 Then
        UserForm1.Label1.Caption = "."
    Else
        UserForm1.Label1.Caption = UserForm1.Label1.Caption & "."
    End If
    Application.OnTime Now + TimeValue("00:00:01"), "loadingdots"
End If

End Sub

Next, call the self-activating sub when the UF gets initialised

Private Sub UserForm_Initialize()
    Call loadingdots
End Sub

Do not forget to change the references to the UF to the right name.