Code execution error

T

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
 
R

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
 
T

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?
 
R

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
 
T

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top