Excel 2010 VBA code is stuck when UserForm is shown

Question

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?

I think you're missing a call to DoEvents. Follow the example here to get this to work: http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/ — Jon Crowell, Nov 15 '12 at 22:48
There was a call to DoEvents within the GetWebData() routine. I've edited my original question to include the code for GetWebData(). Note that the progress indicator UserForm also includes some calls to DoEvents as well. — Denis, Nov 15 '12 at 22:57
`GetWebData (Range("B2").Value)` should be `GetWebData Range("B2").Value` or use `Call GetWebData(...)` if you want parentheses. Unlikely to make a difference here though — barrowc, Nov 16 '12 at 01:12
FWIW if you use a `WinHTTPRequest` rather than automating Internet Explorer, it will be fast enough not to need a progress bar — SWa, Nov 16 '12 at 09:36

score 0 · Answer 1 · edited May 23 '17 at 12:29

If you're not married to using Excel Web Query, and you just want a progress bar to appear while your data loads, you might try opening the URL as a separate Workbook instead. If you do, Excel gives you a progress bar for free.

See my answer on this question: How can I post-process the data from an Excel web query when the query is complete?

The tradeoff of that approach is you have to manage processing the data you get back yourself - Excel won't put it in a given destination for you.

We ended up going this route after we tried something pretty similar to what you seem to have been doing.

Excel 2010 VBA code is stuck when UserForm is shown

1 Answers1

Linked