I've created a form that is used to take user credentials, login our company's system and request exports. When the "Get Exports" button is clicked I display the gif (spinning wheel) and the text (first 'Establishing Connection' for when starting Internet Explorer instance, then it's supposed to change to 'Validating User Credentials'). The problem I'm running into is the gif isn't playing consistently and throughout the code execution after it is created/shown as I'd like it to.
Form before click
Userform code:
Dim progresslbl As Object, progressbar As Object
Dim webBr As Object
'vbNewLine for linebreak in msgbox
Private Sub GetExports_Click()
'Call FetchExports
number = login.number.Text
username = login.username.Text
password = login.password.Text
Label2.Enabled = False
Label4.Enabled = False
login.number.Enabled = False
login.username.Enabled = False
login.password.Enabled = False
login.Height = login.Height * 1.02
Set webBr = login.Controls.Add("Shell.Explorer.2", "x", True)
With webBr
.Navigate "C:\Users\Me\Dropbox\Macros\loadspinner.html"
.Width = 12
.Height = 12
.Top = Label4.Top + 30
.Left = 17.25
.Visible = True
End With
Set progresslbl = login.Controls.Add("Forms.Label.1", "Progress", True)
With progresslbl
.Caption = "Establishing Connection"
.Width = 150
.Height = 21.75
.Top = Label4.Top + 31.25
.Left = 32
.Visible = True
End With
Do While webBr.Busy Or webBr.ReadyState <> READYSTATE_COMPLETE
DoEvents 'Starts the loadspinner gif
Loop
DoEvents
'Call IE instance
Call GetIE
DoEvents
'Wait for system to get successful instance via loop that tests for control and number of tabs
progresslbl.Caption = "Validating User Credentials"
login.Height = login.Height * 1.04
Set progressbar = login.Controls.Add("Forms.Label.1", "Progressbar", True)
With progressbar
.BackColor = RGB(29, 104, 176)
.Width = 10 '-> 150 finish
.Height = 7.75
.Top = Label4.Top + 47 '280
.Left = 17.25
.Visible = True
End With
DoEvents
' Do While progressbar.Width <> 150
' Application.Wait (Now + TimeValue("0:00:01"))
' progressbar.Width = progressbar.Width + 5
' DoEvents
' Loop
'Proceed with login credentials and wait until we successfully get in (and don't receive popup)
End Sub
Module code: (I'm using IE Medium and this method due to my company's connection security sometimes losing connection with the object)
Sub GetIE()
Dim targetURL As String: targetURL = "http://companywebsite.com"
Set IE = New InternetExplorerMedium
IE.Visible = True
IE.Navigate targetURL
' Wait while IE loading...
On Error Resume Next
While IE.Busy
DoEvents
Wend
Do
Set sh = New Shell32.Shell
For Each eachIE In sh.Windows
If InStr(1, eachIE.LocationURL, targetURL) Then
Set IE = eachIE
'IE.Visible = False 'This is here because in some environments, the new process defaults to Visible.
Exit Do
End If
Next eachIE
Loop
Set eachIE = Nothing
Set sh = Nothing
While IE.Busy ' The new process may still be busy even after you find it
DoEvents
Wend
End Sub
Form after click
Scenario 1: The spinning wheel gif doesn't show until after GetIE()
is complete
Scenario 2: The spinning wheel gif displays when it's first created, but the text stays stuck at 'Establishing Connection' even after GetIE()
completes
I tried using the method provided in this answer, but I couldn't get it working. Maybe it's because I'm using late binding?
My Questions:
- How can I accomplish having the gif display (and play) continuously once I create/show it?
- Am I using
DoEvents
correctly? I feel like I might be overusing it? How should I be using usingDoEvents
? - How can I make sure the text next to the gif changes accordingly (as each sub executes, example:
GetIE()
) and doesn't wait until the end and skips (or goes through the other text fast)?