1

I've looked around, and can't get to the heart of the issue.

When stepping through the following code, I get the error

Can't execute code in break mode

My workbook does not have any Worksheet_Change events, or any other macros running in the background. I've disabled all possible volatile functions, etc.

Sub replace_OPEN()
Dim lastCol&, endRow&, i&
Dim mainWS As Worksheet
Set mainWS = Sheets("Scoring page")
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

With mainWS
For i = 1 To lastCol Step 2
    endRow = .Cells(.Rows.Count, i).End(xlUp).Row
    For Each cel In .Range(.Cells(2, i), .Cells(endRow, i))
        cel.Select
        If cel.Value = "[OPEN]" Then ' ERROR POPS UP AT THIS LINE !!!!!!
            cel.Select
            If cel.Offset(1, 0).Value = "" Then
                cel.Value = "[EMPTY]"
                cel.Offset(1, 0).Value = "[FILLED IN]"
                cel.Offset(0, 1).Value = 0
                cel.Offset(1, 1).Value = 3
            End If
        End If
    Next cel
Next i
End With
End Sub

I'm more than aware not to use .Select, but I like to use it while stepping through macros, so I can double check the right things are being worked on, before releasing the entire macro on my sheet. This thread kind of helped, but again, I don't have any extra code in there, nor do I have any On Error Goto anywhere.

I could, I suppose, send that info to Debug.Print, but I'm curious how to track down the Can't Execute... error. Could it be something in the Personal.xlsb macros, as I have other things there...

I've even pressed CTRL+PAUSE BREAK a couple times, as that's worked in the past, IIRC. I'm also very aware of this super common issue - I've looked around to troubleshoot, but nothing that's worked for others has worked for this spreadsheet.

Thanks for any ideas.

Edit: Sometimes it throws the error on the very first run of the macro. Then I hit STOP, run again and it stops the first time. ...or vice versa. Can't get it to be consistent.

Edit2: Giving this more thought, It's 80%+ likely it's my Personal.xlsb but I don't have any On ____ Change macros, so wouldn't think it would cause an issue. Hm.

Community
  • 1
  • 1
BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • 1
    Do you have PESONAL.XLSB with perhaps something running there? I put your code in an empty workbook, entered value [OPEN] in a cell and it runs fine for me. . – mrbungle Jun 13 '16 at 21:08
  • @mrbungle - I'm afraid it will be something in `Personal.XLSB`...except, those are only "straight macros", meaning I don't have any `On ___ Change` events. You have to call the macros individually. I do have some functions too, but would think the same - unless I'm using those, they won't cause issues. – BruceWayne Jun 13 '16 at 21:11
  • 1
    Test the premise; comment all the macros in `Personal.xlsb` and try again; if it works, uncomment them one by one until you can pinpoint what's happening - I've tried the code as well and had no issues with it running as expected – Dave Jun 13 '16 at 21:17
  • 1
    Sometimes this happens if you have personalized ribbons events, it can't execute the life cycle of those hence the message box. As I can spot in the comments, using a Personal.XLSB COULD be the issue as well, but, it would be weird too if you are not using events there either. PS: The posted code has nothing to do with it, it has to be something else in the lifecycle. Something that relies on Excel events, but, is not in VBA. – Sgdva Jun 13 '16 at 21:51
  • 1
    I know you've said you don't have any on events, but maybe you could try disabling events with Application.EnableEvents = False – SSlinky Jun 14 '16 at 07:24
  • Thanks to everyone's response - It's 99% likely it's in the `Personal.XLSB`. Thanks very much for your tips and comments! – BruceWayne Jun 15 '16 at 19:13

0 Answers0