Send email - Non Worksheet Change event

[sachavez]

I have a workbook with a summary sheet showing status of various worksheets (complete or not complete). Each worksheet is updated by a different user. In the summary sheet, I have a formula that says “Completed” when all of the worksheets have been completed.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Basic Summary set-up is:
<o></o>
A1: Workbook Status B1: formula to show Completed or “” (blank)
A5: Sheet1: Completed
A6: Sheet2: Not Complete
A7: Sheet3: Not Complete
Same concept for next 20 sheets….
<o></o>
The formula in B1 of the Summary sheet does not result in a Worksheet Change since everything is formula driven.
<o></o>
I found and modified the following code on the Internet (source unknown). The code works well when a Worksheet Change actually occurs; however, since there is no Worksheet Change, the code does not work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OLook As Object 'Outlook.Application
Dim Mitem As Object 'Outlook.Mailitem
Dim SendAnEmail As Boolean
'If the target cell's value is not = to "Ready" then exit sub
If Range("Test") <> "Completed" Then Exit Sub
If Range("Test") = "Completed" Then SendAnEmail = True
'If the SendAnEmail variable is true then this code sends an email
'from Outlook. Change the properties to suit your needs.
If SendAnEmail Then
Set OLook = CreateObject("Outlook.Application")
Set Mitem = OLook.CreateItem(0)
Mitem.to = "xyz@xyz.com"
Mitem.Subject = "Spreadsheet XYZ is ready"
Mitem.Body = "Spreadsheet XYZ is ready for you"
Mitem.Send
End If
'Destroy any object references
Set OLook = Nothing
Set Mitem = Nothing
End Sub

<o></o>
I have searched the Internet for a solution, but have not yet found one. Can anyone help with a workaround that will trigger the email send event?
<o></o>
Thanks!

 

[StuLux]

How about having a worksheet_change routine on each of the worksheets that runs this macro, that way whenever somebody changes something on their sheet it will check to see whether the whole book is completed but not do anything until the last sheet is done? I'm sure there's a more elegant solution but this sounds like it would work to me?

 

[sachavez]

How about having a worksheet_change routine on each of the worksheets that runs this macro, that way whenever somebody changes something on their sheet it will check to see whether the whole book is completed but not do anything until the last sheet is done? I'm sure there's a more elegant solution but this sounds like it would work to me?

Stuart, let me look at that and advise.

Thanks for your input.