[vba] 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!

This question is related to vba excel messagebox

The answer is


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.


Essentially you want to add code to the Calculate event of the relevant Worksheet.

In the Project window of the VBA editor, double-click the sheet you want to add code to and from the drop-downs at the top of the editor window, choose 'Worksheet' and 'Calculate' on the left and right respectively.

Alternatively, copy the code below into the editor of the sheet you want to use:

Private Sub Worksheet_Calculate()

If Sheets("MySheet").Range("A1").Value > 0.5 Then
    MsgBox "Over 50%!", vbOKOnly
End If

End Sub

This way, every time the worksheet recalculates it will check to see if the value is > 0.5 or 50%.


I don't think a message box is the best way to go with this as you would need the VB code running in a loop to check the cell contents, or unless you plan to run the macro manually. In this case I think it would be better to add conditional formatting to the cell to change the background to red (for example) if the value exceeds the upper limit.


Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?

Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to messagebox

HTML - Alert Box when loading page Close a MessageBox after several seconds How to pop an alert message box using PHP? C# MessageBox dialog result VBA code to show Message Box popup if the formula in the target cell exceeds a certain value ASP.NET Web Application Message Box How to get text and a variable in a messagebox How to add message box with 'OK' button? How to get DataGridView cell value in messagebox? MessageBox Buttons?