当单元格选择发生更改时，自动调用 vba 中的子例程?

Question

这个公式还能修改吗？

1. 我一直在尝试比较三个显示"是"或"否"的下拉列表。但是，每当我在 excel 中进行更改时，即使我尝试调用特定的子名称，它也不会自动激活或调用子例程。

2. 你能帮我解决这个问题吗(注意：我什至让我的 If Then Else 语句这么长，因为字符串参数似乎区分大小写。

   Sub discountCheck()
   Sheets("Data - Discount").Select
   Dim i As Integer
   Dim xrow As Integer, yrow As Integer, zrow As Integer
   Dim aa As String, bb As String, cc As String
   xrow = 2
   yrow = 2
   zrow = 2
   For i = 1 To 4
   aa = Cells(xrow, 8).Value
   bb = Cells(yrow, 9).Value
   cc = Cells(zrow, 10).Value
   ' Yes / Yes / No
   If aa = "Yes" And bb = "Yes" And cc = "No" Then
   MsgBox ("Invalid discount placement! Check Invoice #: " & Cells(i + 1, 2).Value)
   Exit Sub
   Else
   ' Yes / yes / No
   If aa = "Yes" And bb = "yes" And cc = "No" Then
   MsgBox ("Invalid discount placement! Check Invoice #: " & Cells(i + 1, 2).Value)
   Exit Sub
   Else
   ' yes / Yes / No
   If aa = "yes" And bb = "Yes" And cc = "No" Then
   MsgBox ("Invalid discount placement! Check Invoice #: " & Cells(i + 1, 2).Value)
   Exit Sub
   Else
   ' Yes / No / Yes
   If aa = "Yes" And bb = "No" And cc = "Yes" Then
   MsgBox ("Invalid discount placement! Check Invoice #: " & Cells(i + 1, 2).Value)
   Exit Sub
   Else
   ' Yes / No / yes
   If aa = "Yes" And bb = "No" And cc = "yes" Then
   MsgBox ("Invalid discount placement! Check Invoice #: " & Cells(i + 1, 2).Value)
   Exit Sub
   Else
   ' yes / No / Yes
   If aa = "yes" And bb = "No" And cc = "Yes" Then
   MsgBox ("Invalid discount placement! Check Invoice #: " & Cells(i + 1, 2).Value)
   Exit Sub
   Else
   ' no / Yes / Yes
   If aa = "No" And bb = "Yes" And cc = "Yes" Then
   MsgBox ("Invalid discount placement! Check Invoice #: " & Cells(i + 1, 2).Value)
   Exit Sub
   Else
   ' no / Yes / yes
   If aa = "No" And bb = "Yes" And cc = "yes" Then
   MsgBox ("Invalid discount placement! Check Invoice #: " & Cells(i + 1, 2).Value)
   Exit Sub
   Else
   ' no / yes / Yes
   If aa = "No" And bb = "yes" And cc = "Yes" Then
   MsgBox ("Invalid discount placement! Check Invoice #: " & Cells(i + 1, 2).Value)
   Exit Sub
   End If
   End If
   End If
   End If
   End If
   End If
   End If
   End If
   End If  
   xrow = xrow + 1
   yrow = yrow + 1
   zrow = zrow + 1
   Next i
   End Sub
   

Answer 1

参考 当Excel中的单元格值更改时如何运行宏？

这个想法是利用单元格Change事件。

例：

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Call Mymacro
End If
End Sub