Hello,
I've been reading through many threads to try and troubleshoot my particular problem. I have a worksheet that is updated automatically via formula based on another workbook. The intent is that an email will be sent out that will include cell values relative to the cell that was changed. I was able to get this to work with Worksheet_Change event, however, this was only tested through manually changing the value. I recently learned that the Worksheet_Change only applies to manually changed cells -- not formula-driven ones.
My issue is that with Worksheet_Calculate is that I am unable to define Target in the same way that Worksheet_Change uses it in order to identify which cell was actually changed. Is there a way that I can determine which cell is changed using Worksheet_Calculate so that when any cell in Column "E" changes its formula output value? I want to be able to use cell values in the body of my email. Any help would be great as I try to learn how Excel VBA works.
The variables targNum, targDesc, ... , targStatus are
I've been reading through many threads to try and troubleshoot my particular problem. I have a worksheet that is updated automatically via formula based on another workbook. The intent is that an email will be sent out that will include cell values relative to the cell that was changed. I was able to get this to work with Worksheet_Change event, however, this was only tested through manually changing the value. I recently learned that the Worksheet_Change only applies to manually changed cells -- not formula-driven ones.
My issue is that with Worksheet_Calculate is that I am unable to define Target in the same way that Worksheet_Change uses it in order to identify which cell was actually changed. Is there a way that I can determine which cell is changed using Worksheet_Calculate so that when any cell in Column "E" changes its formula output value? I want to be able to use cell values in the body of my email. Any help would be great as I try to learn how Excel VBA works.
The variables targNum, targDesc, ... , targStatus are
VBA Code:
Dim xRgSel As Range
Dim targNum As String
Dim targDesc As String
Dim targOwner As String
Dim targNotes As String
Dim targStatus As String
'Sub to call functions when detecting a change to the column
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Range("E2:E52")
Set xRgSel = Intersect(Target, xRg)
ActiveWorkbook.Save
If xRgSel Is Nothing Then Exit Sub
'To assign column-specific values to a variable to reference later in the body of the email
targNum = xRgSel.Cells.Offset(0, -4).Value
targDesc = xRgSel.Cells.Offset(0, -3).Value
targOwner = xRgSel.Cells.Offset(0, 1).Value
targNotes = xRgSel.Cells.Offset(0, 2).Value
targStatus = xRgSel.Cells.Offset(0, -1).Value
'MsgBox (targNum)
'MsgBox (targDesc)
'MsgBox (targOwner)
'MsgBox (targNotes)
'Checks if the target is numeric and is equal to 1
If IsNumeric(Target) And Target.Value = "1" Then
Call Mail_Text_Outlook1
End If
'Checks if the target is numeric and is equal to 2
If IsNumeric(Target) And Target.Value = "2" Then
Call Mail_Text_Outlook2
End If
End Sub