如何防止工作表更改事件使Excel崩溃? [英] How can I keep my worksheet change event from crashing Excel?

问题描述

！[在此处输入图片描述] [1]我正在使用简单的IF语句构建用于调度棒球比赛的表格. 每个团队总共玩8场比赛，必须打8支不同的球队，其中4支主场和4支客场.

![enter image description here][1]I'm constructing a table for scheduling baseball games using simple IF statements. Each team plays 8 games total, has to play 8 different teams, 4 home and 4 away.

这个想法是，如果在B3中输入1(A在其中B)，则单元格C2将填充0，因为两支球队不能两次比赛.

The idea is that if a 1 is entered in B3 (Where A plays B), cell C2 fills with a 0 since two teams can't play eachother twice.

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B3").Value = 1 Then Range("C2").Value = 0
If Range("B4").Value = 1 Then Range("D2").Value = 0
If Range("B5").Value = 1 Then Range("E2").Value = 0

依此类推.如果我开始填写B列，它会陷入某种循环并停止运行，直到我退出Escape或Excel崩溃为止.

So on and so forth. If I start filling out column B, it runs into some sort of loop and stops functioning until I bang escape or Excel crashes.

如果我使用

Application.EnableEvents = False

代码完全停止工作.如何使该工具即时"运行而不会崩溃?

The code stops working completely. How can I make this tool work "on the fly" without having it crash?

http://i.stack.imgur.com/itgsq.png

P.S.不能使用求解器，因为15x15的更改单元过多

P.S. can't use solver for this because 15x15 is too many change cells

推荐答案

虽然您说过使用过:

Application.EnableEvents = False

您是否使用以下代码将其重新设置为代码的结尾:

Did you set it back at the end of your code with:

Application.EnableEvents = True

将其放在End Sub之前.如果您不这样做，它将不会再次触发.想法是关闭事件以进行更改，然后在下次工作表更新时再次将其重新打开

Put it just before End Sub. If you don't it will not fire again. The idea is turn the events off to make your changes then turn them back on again for the next time the sheet is updated

这篇关于如何防止工作表更改事件使Excel崩溃?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！