Code execution error
 

Hi all,
I have a workbook that has been running fine for a very long time. Last week
I noticed that all of a sudden I am getting an error that states "Code
execution has been interrupted". The macro still completes the operation, but
the error box comes up.
I have not altered the code in any way for quite some time, but this error
mysteriously started happening last week seemingly out of the blue. Any clues
as to what it could be and fixes for this?

Public Sub Print_Visible_Worksheets()
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim arr() As String
Dim N As Long
Const sStr As String = "Main"

For Each sh In ThisWorkbook.Worksheets
With sh
If .Visible = xlSheetVisible Then
If StrComp(.Name, sStr, vbTextCompare) Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = .Name
End If
End If
End With
Next sh

With ThisWorkbook
.Worksheets(arr).PrintOut
.Worksheets(1).Select <-----ERROR IS ON THIS LINE
End With
Application.ScreenUpdating = True
End Sub

Code execution error
 

Hi Tom

Try placing the following line at the start of your code (after your
sub name):

Application.EnableCancelKey = xlDisabled

and see if the error goes away.

Richard




On 4 Feb, 11:24, Tom wrote:
Hi all,
I have a workbook that has been running fine for a very long time. Last week
I noticed that all of a sudden I am getting an error that states "Code
execution has been interrupted". The macro still completes the operation, but
the error box comes up.
I have not altered the code in any way for quite some time, but this error
mysteriously started happening last week seemingly out of the blue. Any clues
as to what it could be and fixes for this?

Public Sub Print_Visible_Worksheets()
* * Application.ScreenUpdating = False
* * Dim sh As Worksheet
* * Dim arr() As String
* * Dim N As Long
* * Const sStr As String = "Main"

* * For Each sh In ThisWorkbook.Worksheets
* * * * With sh
* * * * * * If .Visible = xlSheetVisible Then
* * * * * * * * If StrComp(.Name, sStr, vbTextCompare) Then
* * * * * * * * * * N = N + 1
* * * * * * * * * * ReDim Preserve arr(1 To N)
* * * * * * * * * * arr(N) = .Name
* * * * * * * * End If
* * * * * * End If
* * * * End With
* * Next sh

* * With ThisWorkbook
* * * * .Worksheets(arr).PrintOut
* * * * .Worksheets(1).Select *<-----ERROR IS ON THIS LINE
* * End With
* * Application.ScreenUpdating = True
End Sub

Code execution error
 

Hi Richard,
That seems to fix the problem. Now, in order that I can understand
this....WHY would this fix the problem...and why, all of a sudden, would it
start causing a problem to begin with?
I have had this macro written for almost a year without this error...so why
did it start all of a sudden?

"RichardSchollar" wrote:

Hi Tom

Try placing the following line at the start of your code (after your
sub name):

Application.EnableCancelKey = xlDisabled

and see if the error goes away.

Richard




On 4 Feb, 11:24, Tom wrote:
Hi all,
I have a workbook that has been running fine for a very long time. Last week
I noticed that all of a sudden I am getting an error that states "Code
execution has been interrupted". The macro still completes the operation, but
the error box comes up.
I have not altered the code in any way for quite some time, but this error
mysteriously started happening last week seemingly out of the blue. Any clues
as to what it could be and fixes for this?

Public Sub Print_Visible_Worksheets()
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim arr() As String
Dim N As Long
Const sStr As String = "Main"

For Each sh In ThisWorkbook.Worksheets
With sh
If .Visible = xlSheetVisible Then
If StrComp(.Name, sStr, vbTextCompare) Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = .Name
End If
End If
End With
Next sh

With ThisWorkbook
.Worksheets(arr).PrintOut
.Worksheets(1).Select <-----ERROR IS ON THIS LINE
End With
Application.ScreenUpdating = True
End Sub

Code execution error
 

Tom

I'm afraid I can't answer that - there's nothing wrong with your
code. It seems to be an Excel bug, and disabling the cancel key seems
to be a workaround for it.

Sorry I can't be any more helpful than that!

Richard


On 4 Feb, 11:55, Tom wrote:
Hi Richard,
That seems to fix the problem. Now, in order that I can understand
this....WHY would this fix the problem...and why, all of a sudden, would it
start causing a problem to begin with?
I have had this macro written for almost a year without this error...so why
did it start all of a sudden?



"RichardSchollar" wrote:
Hi Tom

Try placing the following line at the start of your code (after your
sub name):

Application.EnableCancelKey = xlDisabled

and see if the error goes away.

Richard

On 4 Feb, 11:24, Tom wrote:
Hi all,
I have a workbook that has been running fine for a very long time. Last week
I noticed that all of a sudden I am getting an error that states "Code
execution has been interrupted". The macro still completes the operation, but
the error box comes up.
I have not altered the code in any way for quite some time, but this error
mysteriously started happening last week seemingly out of the blue. Any clues
as to what it could be and fixes for this?

Public Sub Print_Visible_Worksheets()
* * Application.ScreenUpdating = False
* * Dim sh As Worksheet
* * Dim arr() As String
* * Dim N As Long
* * Const sStr As String = "Main"

* * For Each sh In ThisWorkbook.Worksheets
* * * * With sh
* * * * * * If .Visible = xlSheetVisible Then
* * * * * * * * If StrComp(.Name, sStr, vbTextCompare) Then
* * * * * * * * * * N = N + 1
* * * * * * * * * * ReDim Preserve arr(1 To N)
* * * * * * * * * * arr(N) = .Name
* * * * * * * * End If
* * * * * * End If
* * * * End With
* * Next sh

* * With ThisWorkbook
* * * * .Worksheets(arr).PrintOut
* * * * .Worksheets(1).Select *<-----ERROR IS ON THIS LINE
* * End With
* * Application.ScreenUpdating = True
End Sub- Hide quoted text -

- Show quoted text -

Code execution error
 

I guess I'll just put it under the "strange but true" category and just
accept it.
Thanks for the fix to the problem Richard...it's very much appreciated.

"RichardSchollar" wrote:

Tom

I'm afraid I can't answer that - there's nothing wrong with your
code. It seems to be an Excel bug, and disabling the cancel key seems
to be a workaround for it.

Sorry I can't be any more helpful than that!

Richard


On 4 Feb, 11:55, Tom wrote:
Hi Richard,
That seems to fix the problem. Now, in order that I can understand
this....WHY would this fix the problem...and why, all of a sudden, would it
start causing a problem to begin with?
I have had this macro written for almost a year without this error...so why
did it start all of a sudden?



"RichardSchollar" wrote:
Hi Tom

Try placing the following line at the start of your code (after your
sub name):

Application.EnableCancelKey = xlDisabled

and see if the error goes away.

Richard

On 4 Feb, 11:24, Tom wrote:
Hi all,
I have a workbook that has been running fine for a very long time. Last week
I noticed that all of a sudden I am getting an error that states "Code
execution has been interrupted". The macro still completes the operation, but
the error box comes up.
I have not altered the code in any way for quite some time, but this error
mysteriously started happening last week seemingly out of the blue. Any clues
as to what it could be and fixes for this?

Public Sub Print_Visible_Worksheets()
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim arr() As String
Dim N As Long
Const sStr As String = "Main"

For Each sh In ThisWorkbook.Worksheets
With sh
If .Visible = xlSheetVisible Then
If StrComp(.Name, sStr, vbTextCompare) Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = .Name
End If
End If
End With
Next sh

With ThisWorkbook
.Worksheets(arr).PrintOut
.Worksheets(1).Select <-----ERROR IS ON THIS LINE
End With
Application.ScreenUpdating = True
End Sub- Hide quoted text -

- Show quoted text -

Code execution error
 

MANY THANKS TOM, BUT WHERE DO I GO TO SET THIS CODE - SORRY BUT I'M NOT AS
COMPUTER LITERATE AS YOU ARE UNFORTUNATELY.

"Tom" wrote:

Hi all,
I have a workbook that has been running fine for a very long time. Last week
I noticed that all of a sudden I am getting an error that states "Code
execution has been interrupted". The macro still completes the operation, but
the error box comes up.
I have not altered the code in any way for quite some time, but this error
mysteriously started happening last week seemingly out of the blue. Any clues
as to what it could be and fixes for this?

Public Sub Print_Visible_Worksheets()
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim arr() As String
Dim N As Long
Const sStr As String = "Main"

For Each sh In ThisWorkbook.Worksheets
With sh
If .Visible = xlSheetVisible Then
If StrComp(.Name, sStr, vbTextCompare) Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = .Name
End If
End If
End With
Next sh

With ThisWorkbook
.Worksheets(arr).PrintOut
.Worksheets(1).Select <-----ERROR IS ON THIS LINE
End With
Application.ScreenUpdating = True
End Sub

Code execution error
 

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

CAROLYN wrote:

MANY THANKS TOM, BUT WHERE DO I GO TO SET THIS CODE - SORRY BUT I'M NOT AS
COMPUTER LITERATE AS YOU ARE UNFORTUNATELY.

"Tom" wrote:

Hi all,
I have a workbook that has been running fine for a very long time. Last week
I noticed that all of a sudden I am getting an error that states "Code
execution has been interrupted". The macro still completes the operation, but
the error box comes up.
I have not altered the code in any way for quite some time, but this error
mysteriously started happening last week seemingly out of the blue. Any clues
as to what it could be and fixes for this?

Public Sub Print_Visible_Worksheets()
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim arr() As String
Dim N As Long
Const sStr As String = "Main"

For Each sh In ThisWorkbook.Worksheets
With sh
If .Visible = xlSheetVisible Then
If StrComp(.Name, sStr, vbTextCompare) Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = .Name
End If
End If
End With
Next sh

With ThisWorkbook
.Worksheets(arr).PrintOut
.Worksheets(1).Select <-----ERROR IS ON THIS LINE
End With
Application.ScreenUpdating = True
End Sub

--

Dave Peterson