What am I doing wrong?

[London_Calling]

I’ve got a few triggers set up on my worksheet that cause a cell/s to display ‘LAY’ when the criteria are met. When LAY is displayed a simple VBA code is initiated:

Private Sub Worksheet_Calculate()

If [CB5] = "LAY" Then
[S5] = [BG5]
[Q5] = "LAY"
End If

End Sub

That’s all fine. But if I add a row (see below next), it causes the CPU to immediately jump to 100% and Excel freezes indefinitely:

Private Sub Worksheet_Calculate()

If [CB5] = "LAY" Then
[S5] = [BG5]
[Q5] = "LAY"
End If

If [CB6] = "LAY" Then
[S6] = [BG6]
[Q6] = "LAY"
End If

End Sub

Fwiw, the formula code also works fine when I trigger the LAY cells but don't initiate the VBA code.

Is my code wrong, perhaps (though there's no indication of a circular reference) ? Any suggestions and/or ideas gratefully received.

Cheers.

 

[stanleydgromjr]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("CB5:CB6")) Is Nothing Then
        Exit Sub
    End If
    Application.EnableEvents = False

    If [CB5] = "LAY" Then ' Pre-race selections trigger
        [S5] = [BG5]
        [Q5] = "LAY"
    End If
    
    If [CB6] = "LAY" Then ' Pre-race selections trigger
        [S6] = [BG6]
        [Q6] = "LAY"
    End If

    Application.EnableEvents = True

End Sub

Have a great day,
Stan

 

[jc113883]

Sometimes you can get loops occuring in your code without realising. Your macro runs when the worksheet calculates. The code itself might cause the worksheet to calculate and that caused the looping which locks up your computer.

Try putting a breakpoint in the first line of the code. When your macro runs, it should jump to the VBE window. Next, step through your code using F8, one step at a time and see if a loop occurs.

 

[Darren Bartrup]

If, as jc113883 said, the code in the calculate event is causing the worksheet to recalculate (firing the calculate event again) try adding:

Application.EnableEvents = False

Application.EnableEvents = True

at either end of your code.

Edit: I really should try reading all of previous posts. stanleydgromjr has used EnableEvents in his version of the code.

 

[London_Calling]

Goodness . . . guys, guys. I really am very grateful. Thank you all so much.

And Stan – I really will have a great day. What a relief!


Cheers everyone.