What am I doing wrong?

London_Calling

Active Member
Joined
Feb 27, 2007
Messages
256
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:

Code:
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:

Code:
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Joined
Jul 30, 2006
Messages
3,656
Try this:

Code:
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

Board Regular
Joined
May 29, 2006
Messages
165
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

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
If, as jc113883 said, the code in the calculate event is causing the worksheet to recalculate (firing the calculate event again) try adding:
Code:
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

Active Member
Joined
Feb 27, 2007
Messages
256
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.
 

Forum statistics

Threads
1,181,405
Messages
5,929,756
Members
436,687
Latest member
Glass of Gin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top