当范围内的单元格更改并满足给定的条件时，显示Excel VBA消息框 [英] Display Excel VBA message box when a cell within a range changes AND meets a given criteria

问题描述

我需要一些帮助来创建Excel VBA，以便在计算单元格（其中包含公式的单元格）的任何输入范围发生更改并满足范围的给定条件时显示消息框。

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.

例如，范围B2包含作为A2的函数的计算单元，如果在更新输入A2时，重新计算的单元B2超过20％我想用一个消息框来提醒用户。

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.

推荐答案

编辑：Scott提醒我，相交功能比这个更好的 InRange 功能

Scott reminded me of the Intersect function which works out nicer than this InRange function

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.

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

这篇关于当范围内的单元格更改并满足给定的条件时，显示Excel VBA消息框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！