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

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 <T...@discussions.microsoft.com> 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

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 <T...@discussions.microsoft.com> 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
>
>

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 <T...@discussions.microsoft.com> 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 <T...@discussions.microsoft.com> 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 -

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 <T...@discussions.microsoft.com> 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 <T...@discussions.microsoft.com> 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 -
>
>

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

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
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