如果目标单元格中的公式超过特定值，则显示消息框弹出窗口的 VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10013285/
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 code to show Message Box popup if the formula in the target cell exceeds a certain value
提问by Hatt
I am trying to write a simple macro to display a pop-up (vbOKOnly) if the value in a cell exceeds a certain value.
如果单元格中的值超过某个值，我正在尝试编写一个简单的宏来显示弹出窗口（vbOKOnly）。
I basically have a worksheet with products and discounts. I have a formula in one cell, say A1, that shows the discount as a percent (50% or .5) effective discount of all the entries.
我基本上有一个包含产品和折扣的工作表。我在一个单元格中有一个公式，比如 A1，它将折扣显示为所有条目的有效折扣百分比（50% 或 0.5）。
What I'm looking for is code to display a message box if the value of cell A1 exceeds say 50%, because the input of another cell pushed the discount over 50%.
我正在寻找的是在单元格 A1 的值超过 50% 时显示消息框的代码，因为另一个单元格的输入将折扣推高了 50% 以上。
Thanks!
谢谢！
回答by assylias
You could add the following VBA code to your sheet:
您可以将以下 VBA 代码添加到您的工作表中：
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") > 0.5 Then
MsgBox "Discount too high"
End If
End Sub
Every time a cell is changed on the sheet, it will check the value of cell A1.
每次更改工作表上的单元格时，它都会检查单元格 A1 的值。
Notes:
笔记：
- if A1 also depends on data located in other spreadsheets, the macro will notbe called if you change that data.
- the macro will be called will be called every time something changes on your sheet. If it has lots of formula (as in 1000s) it could be slow.
- 如果 A1 还依赖于位于其他电子表格中的数据，则在更改该数据时不会调用宏。
- 每次工作表上的某些内容发生变化时，都会调用宏。如果它有很多公式（如 1000 秒），它可能会很慢。
Widoruses a different approach (Worksheet_Calculateinstead of Worksheet_Change):
Widor使用不同的方法（Worksheet_Calculate而不是Worksheet_Change）：
- Pros: his method will work if A1's value is linked to cells located in other sheets.
- Cons: if you have many links on your sheet that reference other sheets, his method will run a bit slower.
- 优点：如果 A1 的值链接到位于其他工作表中的单元格，他的方法将起作用。
- 缺点：如果您的工作表上有许多引用其他工作表的链接，那么他的方法运行速度会慢一些。
Conclusion: use Worksheet_Changeif A1 only depends on data located on the same sheet, use Worksheet_Calculateif not.
结论：Worksheet_Change如果 A1 仅取决于位于同一工作表上的数据，则使用，Worksheet_Calculate否则使用。
回答by Widor
Essentially you want to add code to the Calculateevent of the relevant Worksheet.
本质上，您希望将代码添加到Calculate相关工作表的事件中。
In the Project window of the VBA editor, double-click the sheet you want to add code to and from the drop-downs at the top of the editor window, choose 'Worksheet' and 'Calculate' on the left and right respectively.
在 VBA 编辑器的“项目”窗口中，在编辑器窗口顶部的下拉列表中双击要添加代码的工作表，分别选择左侧和右侧的“工作表”和“计算”。
Alternatively, copy the code below into the editor of the sheet you want to use:
或者，将以下代码复制到您要使用的工作表的编辑器中：
Private Sub Worksheet_Calculate()
If Sheets("MySheet").Range("A1").Value > 0.5 Then
MsgBox "Over 50%!", vbOKOnly
End If
End Sub
This way, every time the worksheet recalculates it will check to see if the value is > 0.5 or 50%.
这样，每次工作表重新计算时，它都会检查该值是 > 0.5 还是 50%。
回答by Matt Donnan
I don't think a message box is the best way to go with this as you would need the VB code running in a loop to check the cell contents, or unless you plan to run the macro manually. In this case I think it would be better to add conditional formatting to the cell to change the background to red (for example) if the value exceeds the upper limit.
我认为消息框不是最好的方法，因为您需要循环运行 VB 代码来检查单元格内容，或者除非您计划手动运行宏。在这种情况下，我认为如果值超过上限，最好向单元格添加条件格式以将背景更改为红色（例如）。
