Overwriting Excel built in error messages

Question

In relation to my previous question, I am trying to capture connection errors from Data > Connections.

I have added a test connection to check to see if my code works correctly, however, when refreshing the connections, a built in Excel dialog will appear:

Is there anyway to overwrite this dialog with the custom MsgBox as shown:

Dim cn As WorkbookConnection
On Error GoTo ErrorHandler:
For Each cn In ActiveWorkbook.Connections
    cn.Refresh
Next
'On Error GoTo 0
Exit Sub
ErrorHandler:
MsgBox "A connection could not be reached" & ": " & cn.name & ": " & cn.Description

LATEST EDIT: This is how my code currently looks -

Dim cn As WorkbookConnection
Application.DisplayAlerts = False

For Each cn In ActiveWorkbook.Connections
    cn.Refresh
    On Error GoTo ErrorHandler:
Next
'On Error GoTo 0
Exit Sub

Application.DisplayAlerts = True

ErrorHandler:
MsgBox "A connection could not be reached" & ": " & cn.name & ": " & cn.Description

If you read the answer of your previous question well, you will get the answer to your current question. Removing the `On Error GoTo 0` will hook in your handler in which you want to add your custom error message. — Amen Jlili, Oct 13 '15 at 08:29
@Jelly Even when commenting out the On Error GoTo 0, it still throws the above. — Sean, Oct 13 '15 at 08:32
How does your code continue, if you just confirm above message? Does it go to your error handling afterwards? — Verzweifler, Oct 13 '15 at 08:37
@Verzweifler If I confirm the built in dialog, the connections will continue to refresh without an issue. But for one's that cannot be reached, I want my custom error dialog to appear. — Sean, Oct 13 '15 at 08:42
Can you step by step through the code using F8 and see if cn is initialized during the error handling? — Amen Jlili, Oct 13 '15 at 08:44
@Jelly It will step through each line up the the point of Exit Sub. It will disregard my ErrorHandler? — Sean, Oct 13 '15 at 08:46
What happens, if you prevent Excel from opening Dialogs by stating `Application.DisplayAlerts = False` before your loop? Don't forget to set it to `True` afterwards though :-) — Verzweifler, Oct 13 '15 at 08:50
@Verzweifler Would the false go before my 'On Error GoTo ErrorHandler' or in between that and my For Each loop? Because it still throws the built in dialog when trying your suggestion. — Sean, Oct 13 '15 at 08:55
Try to take the error handling block and put it just under the refresh line. — Amen Jlili, Oct 13 '15 at 08:57
@Jelly I have just moved the code around as you've suggested and it is still throwing the original built in dialog. — Sean, Oct 13 '15 at 09:01
It shouldn't matter where the `False` goes as long at it is before `cn.Refresh` - but that seems not to be it. The last thing I can suggest is removing the `:` in your `On Error GoTo`-Statement. — Verzweifler, Oct 13 '15 at 09:05
@Verzweifler removing the ':' throws a "Sub or Function not defined" compile error :( — Sean, Oct 13 '15 at 09:07
@Jelly I would, but company policy refrains from it. The connections are internal... Would there be another alternative? — Sean, Oct 13 '15 at 09:09
The "Sub or Function not defined"-Error seems suspicious to me. Can you confirm that every loop, Sub, Function, if-statements etc. you might have elsewhere are properly closed? — Verzweifler, Oct 13 '15 at 09:13
@Verzweifler I've resolved that now. Not sure why it would occur but see my latest edit for this. I'm struggling but I have to revisit this project... — Sean, Oct 13 '15 at 09:15
The `On Error`-statement needs to be above the line in which you want to capture the error! This way, you activate the statement only after the first connection has refreshed (or tried to do so). Also, `DisplayAlerts = True` is currently sitting in a dead space as it is after `Exit Sub` and before the `ErrorHandler` jump label. — Verzweifler, Oct 13 '15 at 09:20
@Verzweifler So where would the `True` go? It's after `Exit Sub` but not after an `End Sub`. — Sean, Oct 13 '15 at 09:22
Put it directly after the loop - if your code reaches `Exit Sub`, everything below that will be ignored. — Verzweifler, Oct 13 '15 at 09:24
@Verzweifler So if that's the case, why is it still ignoring my `ErrorHandler` below the Exit Sub? I was told to place it after that so that it would prevent a continuous loop within the handler. — Sean, Oct 13 '15 at 09:26
I just found out that the message being displayed is triggered by an asynchronous event, meaning it only triggers after the website has returned an error. That also means that `cn.Refresh` does *not* return an error, thus is not triggering any `On Error`-statements at all! — Verzweifler, Oct 13 '15 at 09:27
@Verzweifler So is it remotely possible to work around an asynchronous event and bypass the dialog? — Sean, Oct 13 '15 at 09:28
Unfortunately, I don't have any experience with async events in VBA. Hopefully, Google knows more about this... Good Luck! — Verzweifler, Oct 13 '15 at 09:31
Connections typically have a "run in background" property: if you set that to False it will run synchronously. — Tim Williams, Oct 13 '15 at 16:20
@TimWilliams That works, thank you. What effect would it have on the connection to set the 'Background Refresh' to false? — Sean, Oct 14 '15 at 08:19

score 0 · Answer 1 · edited May 23 '17 at 10:27

For whatever reason I can 'answer' but not comment -- depending on how many websites you're opening you could throw in a timer loop that essentially pauses until you can conclude that your webpage has loaded -- if in the allotted time it hasn't you can jump to an error, and if it has you can exit that loop.

The negative here is essentially just slowing your run time, but your time loop wouldn't need to be more than a second or two.

VBA Internet Explorer wait for web page to load

I was looking for a more relevant answer and came across this:

Wait until ActiveWorkbook.RefreshAll finishes - VBA

Overwriting Excel built in error messages

1 Answers1