Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Excel UserForm.Show "Code execution has been interruped" error
I have a button on a worksheet that when clicked executes:
The first time the button is clicked, the user form shows. I have a listbox come up and they click the item they want, then click the OK button, which goes through the list box and returns the selected value to the current worksheet.
I am then back at Sheet1 and see my value in cell B2. Then the problem occurs. I click on the button to show the form again and I get the Microsoft Visual Basic Error dialog box with the error:
Code execution has been interruped
If I click continue, everything is fine.
Why am I getting this?
How do I prevent this from happening?
Private Sub cmdGetQuote_Click()
frmSelectQuote.Show
End Sub
The first time the button is clicked, the user form shows. I have a listbox come up and they click the item they want, then click the OK button, which goes through the list box and returns the selected value to the current worksheet.
Private Sub cmdOK_Click()
On Error GoTo ErrHandler
Dim lngItem As Long
Dim I As Long
Dim msg As String
If ListBox1.ListIndex <> -1 Then
For I = 0 To ListBox1.ColumnCount - 1
msg = msg & ListBox1.Column(I) & vbCrLf
Next I
End If
For lngItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lngItem) Then
With Sheets("Sheet1") '< qualify sheet here
.Range("B2").Value = ListBox1.List(lngItem, 0)
End With
End If
Next lngItem
Unload Me
ExitHandler:
Unload Me
Exit Sub
ErrHandler:
MsgBox "Error: " & Err.Number & " " & Err.Description & " :ListBox1_DblClick"
GoTo ExitHandler
Resume
End Sub
I am then back at Sheet1 and see my value in cell B2. Then the problem occurs. I click on the button to show the form again and I get the Microsoft Visual Basic Error dialog box with the error:
Code execution has been interruped
If I click continue, everything is fine.
Why am I getting this?
How do I prevent this from happening?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
I would advise adding two lines to your code as shown below (suffixed with ' *** ADDED) :
---
Private Sub cmdOK_Click()
On Error GoTo ErrHandler
Dim lngItem As Long
Dim I As Long
Dim msg As String
Application.EnableCancelKe
If ListBox1.ListIndex <> -1 Then
For I = 0 To ListBox1.ColumnCount - 1
msg = msg & ListBox1.Column(I) & vbCrLf
Next I
End If
For lngItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lngItem)
With Sheets("Sheet1") '< qualify sheet here
.Range("B2").Value = ListBox1.List(lngItem, 0)
End With
End If
Next lngItem
Application.EnableCancelKe
Unload Me
ExitHandler:
Unload Me
Exit Sub
ErrHandler:
MsgBox "Error: " & Err.Number & " " & Err.Description & " :ListBox1_DblClick"
GoTo ExitHandler
Resume
End Sub
---
This is a recognised "fix" to the error message you have encountered, but comes with a warning:
You will not be able to break ([ CTRL ]+[ BREAK ]) into your code between the two statements as it is executing.
Therefore, please save the code/workbook before attempting re-execution once the lines have been added.
I would advise adding two lines to your code as shown below (suffixed with ' *** ADDED) :
---
Private Sub cmdOK_Click()
On Error GoTo ErrHandler
Dim lngItem As Long
Dim I As Long
Dim msg As String
Application.EnableCancelKe
If ListBox1.ListIndex <> -1 Then
For I = 0 To ListBox1.ColumnCount - 1
msg = msg & ListBox1.Column(I) & vbCrLf
Next I
End If
For lngItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lngItem)
With Sheets("Sheet1") '< qualify sheet here
.Range("B2").Value = ListBox1.List(lngItem, 0)
End With
End If
Next lngItem
Application.EnableCancelKe
Unload Me
ExitHandler:
Unload Me
Exit Sub
ErrHandler:
MsgBox "Error: " & Err.Number & " " & Err.Description & " :ListBox1_DblClick"
GoTo ExitHandler
Resume
End Sub
---
This is a recognised "fix" to the error message you have encountered, but comes with a warning:
You will not be able to break ([ CTRL ]+[ BREAK ]) into your code between the two statements as it is executing.
Therefore, please save the code/workbook before attempting re-execution once the lines have been added.
I used your suggestion, thanks for the help.
I still was getting the error and ended up putting this:
.Range("B2").Activate
before the "end with"
It seems you have to be doing something in the worksheet before you click the button again, or this error continues to come up.
Thanks again for your help.
I still was getting the error and ended up putting this:
.Range("B2").Activate
before the "end with"
It seems you have to be doing something in the worksheet before you click the button again, or this error continues to come up.
Thanks again for your help.
I just encountered this error today and found this solution:
http://stackoverflow.com/questions/2154699/excel-vba-app-stops-spontaneously-with-message-code-execution-has-been-halted
Press "Debug" button in the popup.
Press Ctrl+Pause|Break twice.
Hit the play button to continue.
Save the file after completion.
http://stackoverflow.com/questions/2154699/excel-vba-app-stops-spontaneously-with-message-code-execution-has-been-halted
Press "Debug" button in the popup.
Press Ctrl+Pause|Break twice.
Hit the play button to continue.
Save the file after completion.
Thanks for taking the time to post that approach, jeffreywsmith.
I have had mixed results with that in the past, & often found the issue reoccurs; most often within the intended run-time environment when a user cannot use the "Debug" option (due to the VB[A]Project environment being password protected).
Useful to bypass the issue for the short term, though.
I have had mixed results with that in the past, & often found the issue reoccurs; most often within the intended run-time environment when a user cannot use the "Debug" option (due to the VB[A]Project environment being password protected).
Useful to bypass the issue for the short term, though.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial