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

Question

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

User · Answer

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

User · Answer

You could add the following VBA code to your sheet   Private Sub Worksheet Change ByVal Target As Range      If Range  A1    gt  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

User · Answer

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  gt  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

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

Examples related to vba

Examples related to excel

Examples related to messagebox