Combine VBA declaration and calculation in One

[FGaxha]

I have VBA codes: I'm looking to Call macro only when tow condition are meet: D5<0 and Closing Workbook, then SendEmail. It is working but Im receiving one email when condition D5 <0 and other email when I close workbook: Please help
Sheet2:
Private Sub Worksheet_Calculate()
If IsNumeric(Range("D5")) Then
If Range("D5").Value < 0 Then
'MsgBox "Range D5 has reached is limit of <=0", vbInformation
Call SendEmail

End If
End If

End Sub

Workbook:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
'MsgBox "This Workbook is saved."
Call SendEmail
End Sub

 

[DanteAmor]

In order to know if the mail has already been sent and not send it again when you close the book, we need an available cell.
In the example I put the cell Z5, change that cell for a cell that you have available.

Try this:

Private Sub Worksheet_Calculate()
  If IsNumeric(Range("D5")) Then
    If Range("D5").Value < 0 Then
      'MsgBox "Range D5 has reached is limit of <=0", vbInformation
      Call SendEmail
      
      'In an available cell
      Range("Z5").Value = "sent"
    End If
  End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  ThisWorkbook.Save
  'MsgBox "This Workbook is saved."
  If Range("D5").Value < 0 And Range("Z5").Value = "" Then
    Call SendEmail
  End If
End Sub

In the same panel where you have the BeforeClose event add the following code:

Private Sub Workbook_Open()
  'In an available cell
  Range("Z5").Value = ""
End Sub

 

[FGaxha]

Sir, I appreciate your help:

I applied in the workbook your Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
'MsgBox "This Workbook is saved."
If Range("D5").Value < 0 And Range("Z5").Value = "" Then
Call SendEmail
End If
End Sub
Private Sub Workbook_Open()
'In an available cell
Range("Z5").Value = ""
End Sub

 

[FGaxha]

Sir is not working:

If i include this code on worksheet2 it send me twice email: when value D5 <0 and one when close workbook.

Private Sub Worksheet_Calculate()
If IsNumeric(Range("D5")) Then
If Range("D5").Value < 0 Then
'MsgBox "Range D5 has reached is limit of <=0", vbInformation
Call SendEmail

'In an available cell
Range("Z5").Value = "sent"
End If
End If
End Sub

 

[DanteAmor]

You must refer to the sheet, I don't know the name of your sheet, but I'm going to assume it's "Sheet2", so I'll give you the code but you must adjust the name of your sheet:

Private Sub Worksheet_Calculate()
  If IsNumeric(Sheets("Sheet2").Range("D5")) Then
    If Sheets("Sheet2").Range("D5").Value < 0 Then
      'MsgBox "Range D5 has reached is limit of <=0", vbInformation
      Call SendEmail
      
      'In an available cell
      Sheets("Sheet2").Range("Z5").Value = "sent"
    End If
  End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  ThisWorkbook.Save
  'MsgBox "This Workbook is saved."
  If Sheets("Sheet2").Range("D5").Value < 0 And Sheets("Sheet2").Range("Z5").Value = "" Then
    Call SendEmail
  End If
End Sub

Private Sub Workbook_Open()
  'In an available cell
  Sheets("Sheet2").Range("Z5").Value = ""
End Sub

 

[FGaxha]

Sir,
Nope:
may I send you the template please

 

[FGaxha]

It works only if before closing workbook I am on Sheet2,