Opt Out of a Query.

[Guest]

I have a worksheet with a macro that grabs information from many URL sources.
The macro looks for information within the query date entries after a button
selection. What I would like to incorperate is a button to suspend the
macro. Or even better a message box stating the query is being compiled
with possibly a Cancel button.

 

[Sharad Naik]

You can add a userform, adding a label saying "The Queries are being
refreshed" and a commandbutton with caption.
Define a Public Variable at module level, named say - userCanc e.g.:
Public userCanc as Boolean. Then the commandbutton click code of the
userform
do
userCanc = True
Unload Me

In the code to refressh the Query, you should first show the form,
vbModeless.
D referesh one after another and between every refresh command check the
status
of the userCanc, it it is true then exit sub
e.g.:
If userCanc Then Exit Sub.

But even then the last refresh command given by your code will still be
refreshing.
To stop it you need to double click on the sphere appearing in the staus
bar,
which will show a dialog box where you can click on cancel refresh.

Sharad

 

[Rob van Gelder]

If your query is in fact an Excel QueryTable, then you could check the
Refreshing property of each QueryTable.
To cancel a refreshing QueryTable, run the CancelRefresh method.

Sub AllQueryTablesCancelRefresh()
Dim qtb As QueryTable

For Each qtb In ActiveSheet.QueryTables
If qtb.Refreshing Then qtb.CancelRefresh
Next
End Sub


Function AnyQueryTablesRefreshing() As Boolean
Dim qtb As QueryTable, bln As Boolean

bln = False
For Each qtb In ActiveSheet.QueryTables
If qtb.Refreshing Then
bln = True
Exit For
End If
Next
AnyQueryTablesRefreshing = False
End Function