"Code Execution has been Interupted" Msg Box 1

[wwebb]

How do I stop the &quot;Code Execution has been Interupted&quot; Message box appearing after manually stopping my VBA program? Want to automatically exit the Excel routine by using the <Esc> key. I have tried: &quot;Application.DisplayAlerts=False&quot; with no success.

 

[dsi]

To disable User Control:
ThisWorkbook.UserControl = False

Don't forget to reset it:
ThisWorkbook.UserControl = True

 

[wwebb]

I tried using the code and got an error message:
&quot;Object doesn't suopport this property or method&quot;.

 

[dsi]

Crazy! I knew the property name and the example in help showed using ThisWorkbook. Looking at what it applies to and checking the object browser, it looks like that property belongs to the Application object. In short:

Application.UserControl = True/False

 

[wwebb]

I already tried that --- still get the same message box &quot;Code Execution has been interupted&quot;. When I hit debug - I always seem to end up on an &quot;End With&quot; line. This &quot;end with&quot; is part of a &quot;with&quot; that goes out to the Web and pulls data into a spreadsheet:

&quot;With ActiveSheet.QueryTables.Add(Connection:= ....&quot;
.Name = &quot;Web_Query&quot;
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCell
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = False
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

This &quot;End With&quot; is where I continue to hang up -- when I hit the debug key.

Also - thought there may be some way for me to specially address the &quot;Code Eexeecution...&quot; box itself so it is hidden or does not open. Thx for all your help. Your thoughts would be greatly appreciated.

 

[euskadi]

Quoting from the book of help (with a little modification)

Sub mytest()

On Error GoTo handleCancel
Application.EnableCancelKey = xlErrorHandler
MsgBox "This may take a long time: press ESC to cancel"

Do
  Beep
Loop

handleCancel:
If Err = 18 Then
    MsgBox "You cancelled"
End If

End Sub

This will allow you to escape out of a VBA process and send you to a custom error handler which gives your custom message or even no message at all if you choose. Enjoy!

 

[wwebb]

Fantastic. Took out the beep and the loop. Works Great!!! Thanks so much.