当范围内的单元格更改并满足给定条件时显示 Excel VBA 消息框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12426870/
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
Display Excel VBA message box when a cell within a range changes AND meets a given criteria
提问by jmonger
I need some help creating the Excel VBA to display a message box when a any input range to a calculated cell (cell with a formula in it) changes and meets a given criterion for the range.
我需要一些帮助来创建 Excel VBA，以便在计算单元格（其中包含公式的单元格）的任何输入范围发生更改并满足该范围的给定标准时显示消息框。
For example, the range "B2" contains calculated cells which are a function of "A2", and if, upon updating the input, "A2", the recalculated cell, "B2" exceeds 20%, I want to warn the user with a message box.
例如，范围“B2”包含作为“A2”函数的计算单元格，如果在更新输入“A2”时，重新计算的单元格“B2”超过 20%，我想警告用户一个消息框。
回答by Brad
Edit: Scott reminded me of the Intersectfunction which works out nicer than this InRangefunction
编辑：斯科特让我想起Intersect了比这个InRange函数更好的函数
Edit2: This will allow you to have different rules for different ranges. If the cell which was changed by the user is within one of your controlled ranges then the validation rule for that range is called. Otherwise the function goes on.
Edit2：这将允许您对不同的范围有不同的规则。如果用户更改的单元格在您的控制范围之一内，则调用该范围的验证规则。否则该功能继续。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Range1 As Range, Range2 As Range '...
Set Range1 = Me.Range("A1:A9")
Set Range2 = Me.Range("B1:B9")
'...
If Not intersect(Range1, Target) Is Nothing Then
'Rule for Range1
If Target.Value > 0.2 Then 'put your condition here
MsgBox "You exceeded 20%"
End If
ElseIf intersect(Range2, Target) Is Nothing Then
'Rule for Range2...
'elseif more ranges...
'More rules...
End If
End Sub
回答by Scott Holtzman
UPDATE
更新
This code will only trigger if your input cells change, which is better than just using 'Worksheet_Calulate`:
此代码仅会在您的输入单元格更改时触发，这比仅使用“Worksheet_Calculate”要好：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
myRange = Range("B1:B2") '-> assumes input cells are in B1:B2
If Intersect(myRange, Target) Then
'-> assumes calculated cell in A1
If Range("A1").Value > 0.2 Then MsgBox "Above 20%"
'-> to loop through many cells, do this
Dim cel As Range
For Each cel In Range("A1:A10")
If cel.Value > 0.2 Then MsgBox cel.Address & " Above 20%"
Exit For
Next
End If
End Sub
回答by Sorceri
Here is an example of using the workbook sheet change event that is checking for a change in the A1 cell in sheet 1
这是使用工作簿工作表更改事件的示例，该事件正在检查工作表 1 中 A1 单元格的更改
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
'check to ensure we are working in the correct Sheet
If ActiveWorkbook.ActiveSheet.Name = "Sheet1" Then
'check to see how many cells have been targeted
If Target.Cells.Count = 1 Then
If Target.Cells.Address = "$A" Then
'check to see what value has been entered into the cell
If Target.Value = 20 Then
MsgBox "Alerting the user"
End If
End If
End If
End If
End Sub
