vba 合并两个 Private Sub Worksheet_Change
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11237340/
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
Combine two Private Sub Worksheet_Change
提问by Kenan Fallon
I am using VBA to check a value of a cell and call an email module to email if the cell's value is more than a value.
我正在使用 VBA 检查单元格的值，如果单元格的值大于一个值，则调用电子邮件模块来发送电子邮件。
I want to check multiple cells but understand that it is not possible to have two Private Sub Worksheet_Change in VBA. What is the best way to check multiple cells?
我想检查多个单元格，但明白在 VBA 中不可能有两个 Private Sub Worksheet_Change。检查多个单元格的最佳方法是什么？
Here is the code I am using;
这是我正在使用的代码；
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > 10 Then
Call Mail_small_Text_Outlook
End If
End If
End Sub
Here is another if possible I would like to combine into the one Sub
如果可能的话，这是另一个我想合并到一个 Sub 中
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("B1"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > 20 Then
Call Mail_small_Text_Outlook
End If
End If
End Sub
采纳答案by Enigmativity
How about doing this?
这样做怎么样？
Private Sub Worksheet_Change(ByVal Target As Range)
Call MailAlert(Target, "A1", 10)
Call MailAlert(Target, "B1", 20)
End Sub
Private Sub MailAlert(ByVal Target As Range, ByVal Address As String, ByVal Value As Integer)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range(Address), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > Value Then
Call Mail_small_Text_Outlook
End If
End If
End Sub
回答by Rory
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Taget.Address
Case "$A" 'This will make sure its just one cell and its A1
If IsNumeric(Target.Value) And Target.Value > 10 Then
Call Mail_small_Text_Outlook
End If
Case "$B" 'This will make sure its just one cell and its B1
If IsNumeric(Target.Value) And Target.Value > 20 Then
Call Mail_small_Text_Outlook
End If
'Case ... whatever else you want.
End Select
End Sub
There may be more efficient ways, but this is what first came to mind. Hope this answers your question.
可能有更有效的方法，但这是首先想到的。希望这能回答你的问题。
