Search code examples
vbaexcelmessagebox

VBA code to show Message Box popup if the formula in the target cell exceeds a certain value

I am trying to write a simple macro to display a pop-up (vbOKOnly) if the value in a cell exceeds a certain value.

I basically have a worksheet with products and discounts. I have a formula in one cell, say A1, that shows the discount as a percent (50% or .5) effective discount of all the entries.

What I'm looking for is code to display a message box if the value of cell A1 exceeds say 50%, because the input of another cell pushed the discount over 50%.

Thanks!

Solution

  • You could add the following VBA code to your sheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1") > 0.5 Then
        MsgBox "Discount too high"
    End If
End Sub

    Every time a cell is changed on the sheet, it will check the value of cell A1.

    Notes:

    • if A1 also depends on data located in other spreadsheets, the macro will not be called if you change that data.
    • the macro will be called will be called every time something changes on your sheet. If it has lots of formula (as in 1000s) it could be slow.

    Widor uses a different approach (Worksheet_Calculate instead of Worksheet_Change):

    • Pros: his method will work if A1's value is linked to cells located in other sheets.
    • Cons: if you have many links on your sheet that reference other sheets, his method will run a bit slower.

    Conclusion: use Worksheet_Change if A1 only depends on data located on the same sheet, use Worksheet_Calculate if not.