当Target.Value>1时，VBA消息框代码为何无法正常运行？

问题说明 #

我用Concatenate函数合并6个条目并计算，当出现重复行时，工作表AA1单元格的值会大于1。Excel公式运行正常，但VBA里的一段代码毫无作用——其他操作都能正常执行，唯独这段检测AA1值并弹出提示的代码没反应。我已经用条件格式高亮了重复行，但需要通过消息框告知用户操作错误和解决方法。

无效的代码片段：

Dim fng As Range
Set fng = Range("$AA$1")

If Not Intersect(Target, fng) Is Nothing Then
 If Target.Value > 1 Then
    MsgBox "You already entered this barrel -- Enter another barrel over the duplicate.  If no more barrels, change Grade to X, amounts to 0, and Producer to Z ZZ", vbOKOnly, "OOPS!"
    End If
End If

完整的Worksheet_Change代码 #

Private Sub Worksheet_Change(ByVal Target As Range)

Target.Worksheet.Unprotect Password:="Cami8"

Dim rng As Range
Set rng = Range("F3:F10001")

If Not Intersect(Target, rng) Is Nothing Then
    Target.Offset(0, 4) = Now
    ActiveWorkbook.Save
    
End If

Application.EnableEvents = True
Dim ung As Range
Set ung = Range("J3:J10005")

If Not Intersect(Target, ung) Is Nothing Then
    Target.Offset(-1, -3).Locked = True
End If

Application.EnableEvents = True

Dim wng As Range
Set wng = Range("J3:J10005")

If Not Intersect(Target, wng) Is Nothing Then
    Target.Offset(-1, -4).Locked = True
End If

Application.EnableEvents = True

Dim xng As Range
Set xng = Range("J3:J10005")

If Not Intersect(Target, xng) Is Nothing Then
    Target.Offset(-1, -5).Locked = True
End If

Application.EnableEvents = True

Dim kng As Range
Set kng = Range("J3:J10005")

If Not Intersect(Target, kng) Is Nothing Then
    Target.Offset(-1, -6).Locked = True
End If

Application.EnableEvents = True

Dim qng As Range
Set qng = Range("J3:J10005")

If Not Intersect(Target, qng) Is Nothing Then
    Target.Offset(-1, -7).Locked = True

End If

Application.EnableEvents = True

Dim cng As Range
Set cng = Range("C3:E10001")
If Not Intersect(Target, cng) Is Nothing Then
    Application.EnableEvents = False
        Target.Value = UCase(Target.Value)
        Target.NumberFormat = "General"
    Application.EnableEvents = True
End If
 
Dim sng As Range
Set sng = Range("E3:E10002")

If Not Intersect(Target, sng) Is Nothing Then
 If Len(Target) > 1 Then
    MsgBox "You entered GRADE with a letter and a space -- Click on the cell and enter only a letter", vbOKOnly, "OOPS!"
    End If
End If

Dim fng As Range
Set fng = Range("$AA$1")

If Not Intersect(Target, fng) Is Nothing Then
 If Target.Value > 1 Then
    MsgBox "You already entered this barrel -- Enter another barrel over the duplicate.  If no more barrels, change Grade to X, amounts to 0, and Producer to Z ZZ", vbOKOnly, "OOPS!"
    End If
End If

Target.Worksheet.Protect Password:="Cami8"
End Sub

问题原因及修复方案 #

核心原因 #

Worksheet_Change事件仅在用户手动修改单元格时触发，AA1的值是由公式自动计算更新的，公式变动不会触发Change事件，所以这段检测AA1的代码永远不会执行。

修复方案 #

不需要检测AA1本身，而是在用户修改那6个参与合并的单元格时，直接检查AA1的值是否大于1。具体操作：

1. 先确定参与Concatenate合并的6个单元格的范围（比如假设是A3:F10001，根据实际使用的列调整）
2. 在Change事件中，当用户修改这些范围的单元格时，判断AA1的值
3. 如果AA1>1，弹出提示

替换原代码中fng相关的部分，修改后的代码片段：

' 定义参与合并的6个单元格范围，请根据实际情况修改
Dim mergeRange As Range
Set mergeRange = Range("A3:F10001") ' 替换成你实际用Concatenate合并的列范围

' 当用户修改合并范围内的单元格时，检查AA1的值
If Not Intersect(Target, mergeRange) Is Nothing Then
    If Range("$AA$1").Value > 1 Then
        MsgBox "您已输入过该桶信息——请替换重复的桶信息。若无其他桶可输入，请将等级改为X、数量改为0、生产商改为Z ZZ", vbOKOnly, "操作提示！"
    End If
End If

额外优化建议 #

• 原代码中多次重复定义J3:J10005的Range，可以合并简化，减少冗余：

Dim lockRange As Range
Set lockRange = Range("J3:J10005")

If Not Intersect(Target, lockRange) Is Nothing Then
    Target.Offset(-1, -3).Locked = True
    Target.Offset(-1, -4).Locked = True
    Target.Offset(-1, -5).Locked = True
    Target.Offset(-1, -6).Locked = True
    Target.Offset(-1, -7).Locked = True
End If

• 原代码中多次重复设置Application.EnableEvents = True是多余的，只需在关闭事件后重新打开一次即可。

内容的提问来源于stack exchange，提问作者Michelle Trudeau