Code execution error

[Tom]

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

 

[RichardSchollar]

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

 

[Tom]

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]

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

 

[Tom]

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.

 

[CAROLYN]

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

 

[Dave Peterson]

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.)