Convert Worksheet_Change event to Worksheet_Calculate for Formula-updated Cells (VLOOKUP from another sheet)

baka416

New Member
Joined
May 12, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

baka416

New Member
Joined
May 12, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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 variables to hold cell values related to the cell that was changed.

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
The variables targNum, targDesc, ... , targStatus are variables to hold cell values related to the cell that was changed.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
Will only one cell in the range E2:E52 ever change or will multiple cells change as a result of the formulas?
 

baka416

New Member
Joined
May 12, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Will only one cell in the range E2:E52 ever change or will multiple cells change as a result of the formulas?
Only one cell will change at a time so the statement below shouldn't be a problem if I am correct.

VBA Code:
If Target.Cells.Count > 1 Then Exit Sub
 

baka416

New Member
Joined
May 12, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Only one cell will change at a time so the statement below shouldn't be a problem if I am correct.

VBA Code:
If Target.Cells.Count > 1 Then Exit Sub
Will only one cell in the range E2:E52 ever change or will multiple cells change as a result of the formulas?
Only one cell in the range E2:E52 will ever change at one particular time, but I am having a hard time assigning the changed cell to a variable that I can reference to be used in the body of the email. I know I should be using Worksheet_Calculate() so the trigger event is based on the formula and not by manual input.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
Unfortunately, there is no way to determine which cell in the range will change using a Worksheet_Calculate event. So my suggestion below is a "work-around". It uses a helper range that will hold the values (no formulas) of E2:E52. I have used AA2:AA52 as the helper range. You can change the AA column to whatever empty column works for you. Manually copy E2:E52 and then pastespecial the values only into AA2:AA52. You only need to do this manual copy/pastespecial once. Each time E2:E52 calculates, the macro will loop through AA2:AA52 and compare each value to the corresponding value in E2:E52. When it doesn't find a match, your variables will be defined based on the unmatched cell in E2:E52 and the appropriate macro will be run. Then the macro copies the changed values from E2:E52 to AA2:AA52 ready for the next time the formulas are calculated. I couldn't test the macro because I don't have access to your file but give it a try.
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

Private Sub Worksheet_Calculate()
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With
    Dim rng As Range
    For Each rng In Range("AA2:AA52")
        If rng.Value <> Range("E" & rng.Row).Value Then
            targNum = Range("E" & rng.Row).Offset(0, -4).Value
            targDesc = Range("E" & rng.Row).Offset(0, -3).Value
            targOwner = Range("E" & rng.Row).Offset(0, 1).Value
            targNotes = Range("E" & rng.Row).Offset(0, 2).Value
            targStatus = Range("E" & rng.Row).Offset(0, -1).Value
            If Range("E" & rng.Row).Value = 1 Then
                Call Mail_Text_Outlook1
            ElseIf Range("E" & rng.Row).Value = 2 Then
                Call Mail_Text_Outlook2
            End If
            Exit For
        End If
    Next rng
    Range("AA2:AA52").Value = Range("E2:E52").Value
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub
 
Solution

baka416

New Member
Joined
May 12, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Unfortunately, there is no way to determine which cell in the range will change using a Worksheet_Calculate event. So my suggestion below is a "work-around". It uses a helper range that will hold the values (no formulas) of E2:E52. I have used AA2:AA52 as the helper range. You can change the AA column to whatever empty column works for you. Manually copy E2:E52 and then pastespecial the values only into AA2:AA52. You only need to do this manual copy/pastespecial once. Each time E2:E52 calculates, the macro will loop through AA2:AA52 and compare each value to the corresponding value in E2:E52. When it doesn't find a match, your variables will be defined based on the unmatched cell in E2:E52 and the appropriate macro will be run. Then the macro copies the changed values from E2:E52 to AA2:AA52 ready for the next time the formulas are calculated. I couldn't test the macro because I don't have access to your file but give it a try.
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

Private Sub Worksheet_Calculate()
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With
    Dim rng As Range
    For Each rng In Range("AA2:AA52")
        If rng.Value <> Range("E" & rng.Row).Value Then
            targNum = Range("E" & rng.Row).Offset(0, -4).Value
            targDesc = Range("E" & rng.Row).Offset(0, -3).Value
            targOwner = Range("E" & rng.Row).Offset(0, 1).Value
            targNotes = Range("E" & rng.Row).Offset(0, 2).Value
            targStatus = Range("E" & rng.Row).Offset(0, -1).Value
            If Range("E" & rng.Row).Value = 1 Then
                Call Mail_Text_Outlook1
            ElseIf Range("E" & rng.Row).Value = 2 Then
                Call Mail_Text_Outlook2
            End If
            Exit For
        End If
    Next rng
    Range("AA2:AA52").Value = Range("E2:E52").Value
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub
I posted on here with a problem and I'm leaving with a solution! Thank you mumps!
I realize that by temporarily storing the values in the column, the recalculation will trigger the macro to compare the newly updated column against the old column. Since theoretically only one value should be different at a time, you were able to use a For loop to iterate through both ranges to identify where the change occurred. This is a great workaround between the Worksheet_Change vs. Worksheet_Calculate dilemma.

Thank you senpai.
 

Forum statistics

Threads
1,141,400
Messages
5,706,217
Members
421,433
Latest member
yash0468

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top