VBA 停止计算单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16962683/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Stop calculation of cells
提问by JadedEric
Very new to VBA in Excel, got asked to do some validation on cell change and got a bit stuck.
Excel 中的 VBA 非常新，被要求对单元格更改进行一些验证并有点卡住了。
So, user needs to enter a monetary value into a cell, let's say D16, so I thought I'd hook into the _Change event on the Worksheet which works quite well.
所以，用户需要在一个单元格中输入一个货币值，比如 D16，所以我想我会挂钩到工作表上的 _Change 事件，效果很好。
However, I need the rest of the worksheet to not complete the calculation when an entry has been submitted into D16, basically, when 500000 is entered, other cells gets updated with values from another worksheet.
但是，当条目已提交到 D16 时，我需要工作表的其余部分不完成计算，基本上，当输入 500000 时，其他单元格会使用另一个工作表中的值进行更新。
My code
我的代码
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("D16") Then
Dim numeric
numeric = IsNumeric(Target)
If numeric = False Then
MsgBox "error"
Exit Sub
/// this is where I need to "stop" the calculations from firing
End If
End If
End Sub
回答by Santosh
I hope below code helps. You need to paste this in sheet code section.
我希望下面的代码有帮助。您需要将其粘贴到工作表代码部分。
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Dim rng As Range
Set rng = Range("D16")
If Not Intersect(Target, rng) Is Nothing And IsNumeric(Target) Then
If Target.Value >= 500000 Then
MsgBox "Value is greater than 500000"
End If
End If
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
回答by Bathsheba
Use Application.Calculation = xlCalculationManual.
使用Application.Calculation = xlCalculationManual.
Don't forget to switch it back on again: Application.Calculation = xlCalculationAutomatic.
不要忘记再次打开它：Application.Calculation = xlCalculationAutomatic。
