msgbox popup when a formula result changes...

**behemoth05** · 10-25-2009, 01:41 AM

Been reading for days here to find the answer to this...

Have learned a TON of stuff on the way.

I am looking to have a message box fire when certain cells or "records" change. Problem is, the cells have MIN/MAX formulas that reference cells that have other formulas that reference other cells that have SUM formulas in them... (did that make sense?).

I would like the box to popup with something along the lines of 'You broke a record!'... etc. Down the road I'll try and have it tell me which record.

Can anyone share some input? I've attached the spreadsheet and highlighted the cells I'd like to be notified about when they change.

Thanks in advance!

**rwgrietveld** · 10-25-2009, 02:20 AM

As a starter I would like to understand why you use

=INDEX(D4:D34,MATCH(MAX(D4:D34),D4:D34,1))

MAX(D4:D34) would do the same

**behemoth05** · 10-25-2009, 02:23 AM

Not sure I have an answer for that... just used the same formulas in all 3 boxes... will definitely change that though.

**rwgrietveld** · 10-25-2009, 06:37 AM

Writing such code is not the problem. The problem lies in the fact that it needs to be triggered (event)

As the data is all from other sheets this is a bit of a problem.

If you always use the command button for this this could trigger the code.

Is this always the case?

**behemoth05** · 10-25-2009, 07:53 PM

Yup... nothing in those columns is entered on that worksheet. At the end of each of my managers' shifts, they use that button to go to the daily sheet and enter their results. I would like for that to fire after they enter their numbers and go back to the overall tracking form.

**rwgrietveld** · 10-26-2009, 07:07 AM

The you can call a function that checks the current values with the previous. I saved the previous in a seperate sheet called "Previous"

Sub PB()

Dim HighValues As Range, PrevHigh As Range
Dim LowValues As Range, PrevLow As Range
Dim i As Long

Set LowValues = Worksheets("October").Range("Q5:Q14")
Set HighValues = Worksheets("October").Range("T5:T14")
Set PrevLow = Worksheets("Previous").Range("Q5:Q14")
Set PrevHigh = Worksheets("Previous").Range("T5:T14")

For i = 2 To 10 Step 2
  If HighValues.Cells(i, 1) > PrevHigh.Cells(i, 1) Then
    MsgBox "New High in " & HighValues.Cells(i, 1).Offset(-1, 0) & ", Day " & HighValues.Cells(i, 1).Offset(0, 1) & ", Date " & HighValues.Cells(i, 1).Offset(0, 2)
    PrevHigh.Cells(i, 1) = HighValues.Cells(i, 1)
  End If
  If LowValues.Cells(i, 1) < PrevLow.Cells(i, 1) Then
    MsgBox "New Low in " & LowValues.Cells(i, 1).Offset(-1, 0) & ", Day " & LowValues.Cells(i, 1).Offset(0, 1) & ", Date " & LowValues.Cells(i, 1).Offset(0, 2)
    PrevLow.Cells(i, 1) = LowValues.Cells(i, 1)
  End If
Next i

End Sub