Pop-Up Message Using VBA

[LaurenAshworth95]

Good afternoon all,

I have a workbook and in one of the sheets I need to run a code that will give me a pop up when the value in a box changes and is still >0. I should mention that the box I need to monitor is a formula so I am monitoring the formulas value rather than the formula itself. I have the below which works however when just one formula value changes it gives me a pop up per value. So I change one formula value and get 7 pop ups. I can attach the spreadsheet if needed. Here is the code I am using:

Private Sub Worksheet_Calculate()
Dim r As Range
For Each r In Range("AF4:AF15678")
If r.Value > 0 Then
MsgBox "Please note there are outstanding kits to be returned on this order. Please confirm any remaining returns with customer or BD representative"
End If
Next r
End Sub

Thank you for any help you can provide.

 

[Joe4]

Welcome to the Board!

The issue with the Worksheet_Calculate is that all it can detect that a calculation was updated SOMEWHERE on the sheet. It has no way of telling which cell had the calculation.

Are you actually changing the formula itself, or is it just the value that it is returning that is changing?
If you are manually changing the formula, then you can use the Worksheet_Change event procedure instead, in which you can detect exactly which cell you updated.
If it is the value that is changing (and not the formula), I think we need to come up with a different plan.

What does this formula look like?
Is the value changing because the underlying values are being updated?
If so, how exactly are these underlying values being updated?