Excel 2010 VBA code is stuck when UserForm is shown
- by Denis
I've created a UserForm as a progress indicator while a web query (using InternetExplorer object) runs in the background. The code gets triggered as shown below. The progress indicator form is called 'Progerss'.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = Range("B2").Row And Target.Column = Range("B2").Column Then
Progress.Show vbModeless
Range("A4:A65535").ClearContents
GetWebData (Range("B2").Value)
Progress.Hide
End If
End Sub
What I see with this code is that the progress indicator form pops up when cell B2 changes. I also see that the range of cells in column A gets cleared which tells me that the vbModeless is doing what I want. But then, somewhere within the GetWebData() procedure, things get hung up. As soon as I manually destroy the progress indicator form, the GetWebData() routine finishes and I see the correct results. But if I leave the progress indicator visible, things just get stuck indefinitely.
The code below shows what GetWebData() is doing.
Private Sub GetWebData(ByVal Symbol As String)
Dim IE As New InternetExplorer
'IE.Visible = True
IE.navigate MyURL
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc = IE.document
Dim Rows As IHTMLElementCollection
Set Rows = Doc.getElementsByClassName("financialTable").Item(0).all.tags("tr")
Dim r As Long
r = 0
For Each Row In Rows
Sheet1.Range("A4").Offset(r, 0).Value = Row.Children.Item(0).innerText
r = r + 1
Next
End Sub
Any thoughts?
