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

excel, messagebox, vba

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.

Problem

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!

Original source