0

I am building a patient database. I have code that checks for changes in a specific column. if data in that column reaches a certain range, i make it send an email. Currently when i manually update the column the program works flawlessly, but when i have a date based formula update it - the macro doesn't seem to recognize it.

What could the problem be?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 22 Then Exit Sub
Dim rng As Range
For Each rng In Range("V1:V14")
   If (rng.Value < 5 And rng.Value > 1) Then
       Call mymacro(rng.Address)
   End If
Next rng
End Sub

Private Sub mymacro(theValue As String)
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
          "The patient that is due is in cell: " & theValue
On Error Resume Next
With xOutMail
    .To = "email@hotmail.com"
    .CC = ""
    .BCC = ""
    .Subject = "Upcoming Patient"
    .Body = xMailBody
    .Display   'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

the formula that updates the column is

=IF(P7<>"",(P7-TODAY()),"")
Alexey Gurevich
  • 1
  • 1
  • 3
    If you are trying to catch when a formula recalculates, try the Worksheet_Calculate event (may be called something slightly different) – Mistella Aug 09 '18 at 13:28
  • 4
    Possible duplicate of [VBA code doesn't run when cell is changed by a formula](https://stackoverflow.com/questions/11406628/vba-code-doesnt-run-when-cell-is-changed-by-a-formula) – BigBen Aug 09 '18 at 13:28
  • @Mistella I am quite a novice in this stuff. What exactly would i sub that for? Worksheet_change? – Alexey Gurevich Aug 09 '18 at 16:08
  • 1
    @AlexeyGurevich Yes, if you're okay with ignoring manual updates. If you want to catch both, you should duplicate the code with `Worksheet_Calculate` being substituted for `Worksheet_Change` for one of them. There are some other helpful answers on the question @BigBen linked to. – Mistella Aug 09 '18 at 17:27

0 Answers0