Well-used simple macro suddenly goes wrong

[martnal]

EXcel 2003. I run a macro each day as I have for many months, on a large sheet, 15,000 rows, 50 columns. It just reformats the text, changes the width of columns, adds a bit of conditional formatting etc., nothing sophisticated. The sheet is generated overnight from a SQL database.

Yesterday the macro stopped, 'Code Execution hs been interrupted.' I selected 'Continue' and all was OK. Today it was interrupted several times as above, but still ran OK. Clearly something is wrong, but I've no idea what. The first interruption is on the line below, .Name = "Courier"

This is the first dozen or so lines. Any help will be most welcome. The highlighted line changes the font from Arial to Courier.

ActiveWindow.DisplayGridlines = True
'
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Cells.Select
With Selection.Font
.Name = "Courier"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With

 

[Andrew Poulsom]

Try putting this at the beginning of your procedure:

Application.EnableCancelKey = xlDisabled

 

[TMShucks]

You can make this code faster by not selecting rows, columns and cells:

ActiveWindow.DisplayGridlines = True
'
Rows("1:1").Delete Shift:=xlUp
With Cells.Font
    .Name = "Courier"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
End With

And it would make your code more concise, if not much quicker, if you don't specify all the default parameters:

ActiveWindow.DisplayGridlines = True
'
Rows("1:1").Delete Shift:=xlUp
With Cells.Font
    .Name = "Courier"
    .Size = 10
End With

 

[martnal]

Andrew, thank you, that seems to have fixed the problem. Could you explain what it actually does, and give me any idea what caused this problem to occur?

Martin

 

[Andrew Poulsom]

Andrew, thank you, that seems to have fixed the problem. Could you explain what it actually does, and give me any idea what caused this problem to occur?

Martin

It prevents the code from being interrupted by pressing Esc or Ctrl+Break. The problem is quite common but I have never seen a cause given.