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

vba excel messagebox
Hatt picture Hatt · Apr 4, 2012 · Viewed 231.6k times · Source

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!

Answer

assylias picture assylias · Apr 4, 2012

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.

Related questions

Display a message box with a timeout value

The question comes from code like this. Set scriptshell = CreateObject("wscript.shell") Const TIMEOUT_IN_SECS = 60 Select Case scriptshell.popup("Yes or No? leaving this window for 1 min is the same as clicking Yes.", TIMEOUT_IN_SECS, "popup window", vbYesNo + …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More