If statement in Worksheet_Change not working with On Error Resume Next

Question

I pull river level data from the National Weather Service with Power Query.

I want to use VBA to automatically send me an email if the river level exceeds a certain level.

Dim xRg As Range

Private Sub Worksheet_Change(ByVal Target As Range)
   On Error Resume Next
   Set xRg = Intersect(Range("B2"), Target)
   If xRg Is Nothing Then Exit Sub
   If IsNumeric(Target.Value) And Target.Value > 15 Then
      Call Mail_small_Text_Outlook
   End If
End Sub

When the Power Query refreshes, it triggers the Worksheet_Change event. This sends an email regardless of cell B2's value. I'd like it to send an email only when the value exceeds 15.

I tried adding the following code to resolve the issue.

If IsNumeric(Target.Value) And Target.Value <= 15 Then Exit Sub

The above code did stop the event from sending an email when the value does not exceed 15, but it also stopped the event from triggering when the Power Query is refreshed.
I need the Worksheet_Change event to fire whenever the Power Query refreshes.

If I resolve this issue, I'm hoping to add something to turn off the Worksheet_Change event to stop it from sending the email more than once.

Answer 1

• Avoid using "On Error Resume Next" unless you are certain that a specific error is being intentionally ignored.
• If no other procedures (Sub/Function) access the variable xRg, consider moving the Dim statement into the Worksheet_Change event.
• If Target is a multiple-cell Range, Target.Value is equivalent to Target.Cells(1).Value. For example, if PQ updates A2:K5, then Target.Value is the content of cell A2 instead of B2.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Me.Range("B4") <> "Yes" Then Exit Sub ' switch of mailing
   Dim xRg As Range
   Set xRg = Intersect(Me.Range("B2"), Target)
   If xRg Is Nothing Then Exit Sub
   If IsNumeric(Me.Range("B2").Value) And Me.Range("B2").Value > 15 Then
      Call Mail_small_Text_Outlook
   End If
End Sub

---

Question: add a button or something like that to turn off the Worksheet_Change event and stop it from sending out the email more than once

• Add a ActiveX commandbutton on sheet (Sheet1)

• Add event code in ThisWorkbook module.

Private Sub Workbook_Open()
    Sheets("Sheet1").CommandButton1.Caption = "Click to DisableEvents"
End Sub

• Add event code in Sheet1 module

Private Sub CommandButton1_Click()
    With Application
        ' Update CommandButton1 caption
        Sheets("Sheet1").CommandButton1.Caption = "Click to " & IIf(.EnableEvents, "EnableEvents", "DisableEvents")
        ' Switch setting
        .EnableEvents = Not .EnableEvents
        Me.Range("E1").Value = .EnableEvents  ' for demo
    End With
End Sub
' Demo event code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "You select cell " & Target.Address
End Sub