How do I get Excel to notify me ( by e-mail) when changes are made

[Guest]

I would like to be notifyed when changes are made to a worksheet. I have a
spreadheet on a server that gets updated daily by several people and I would
like to know when all of them have updated their data so I don't have to call
all of them and ask them to do it.

 

[David McRitchie]

Hi Bart,

You can use an Event macro, the following requires each of three people
to fill in cell in their respective positions within B2:B4

Install by right click on the worksheet, View code, insert the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim str As String
If Not Intersect(Range(Target(1).Address), Range("B2:B4")) Is Nothing Then
On Error Resume Next
If Range("B2:B4").SpecialCells(xlCellTypeBlanks).Count = 0 Then
MsgBox "spy mission completed"
str = "mailto:[email protected]?subject=spy mission completed&body=" _
& "%0D%0A" & "We're finished, now it's your turn." _
& "%0D%0A " & Application.ActiveWorkbook.FullName
ActiveWorkbook.FollowHyperlink (str)
' Application.SendKeys "%s" '--Spell check would stop you anyway
End If
On Error GoTo 0
End If
End Sub