Using Worksheet_Update to run Macro

duhafnusa4

New Member
Joined
May 13, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone! Ive been a long time lurker and now member!

I need some help.

I have a workbook that I have send an email when a group of cells updates its value to ones I specify (list of people on my team I want the email sent to)

Using worksheet_change it works flawlessly when I manually change the cell to the new value.

The issue is the cell contains an equation and its result is what I want to trigger the macro to run

Here is what I have that works by manually changing the Cell Value (with target.value names changed to name1, name 2 etc.., and e to protect identity:
VBA Code:
Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = True
'If Not Intersect(Target, Range("B41:K41")) Is Nothing Then
'If Target.Value = "name1" Or Target.Value = "name2" Or Target.Value = "name3" Or Target.Value = "name4" Or Target.Value = "name5" Then
If Not Intersect(Target, Range("B49:K49")) Is Nothing Then
If Target.Value = "0" Or "1" Or "2" Or "3" Or "4" Or "5" Then
'Call Email_List
'Call Cell_List
Call Check_Parts_Running
End If
End If
End Sub

Also if I could have Target.Value look at a dynamic list (of members I choose to add/remove that would be nice.

Doing this seems to not work:

VBA Code:
Dim Members As Range
Members = Range ("A1:A6")

If Target.Value = Members Then

To recap:

1)
I want to have an email sent out when a cell that contains a formula updates to a specific result
2) I want to have those specific results in a list I can change depending if my team members change

All of this is to notify my team members when a project they are involved with is currently being worked on (cell with formula is pulling data from PI Datalink)

Thanks!

p.s I suck at excel
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
VBA Code:
Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = True
If Not Intersect(Target, Range("B49:K49")) Is Nothing Then
If Target.Value = "0" Or "1" Or "2" Or "3" Or "4" Or "5" Then
Call Email_List
Call Cell_List
Call Check_Parts_Running
End If
End If
End Sub
 
Upvote 0
This seems to work for

condition 1 (any cell changes in range B5:K5)

But does not work for condition 2 (cells in range B3:K3 Change to equation result "4")


VBA Code:
Private Sub Worksheet_Calculate()
Dim aRange As Range: Set aRange = Range("B5:K5")
Dim bRange As Range: Set bRange = Range("B3:K3")
If Not Application.Intersect(ActiveCell, aRange) Is Nothing Then
Call Check_Parts_Running
If Not Application.Intersect(ActiveCell, bRange) Is Nothing Then
If Target.Value = "4" Then
Call Check_Trial_Running
End If
End If
End If
End Sub
 
Upvote 0
This also works for Condition 1


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rDependents As Range
    On Error Resume Next
    Set rDependents = Target.Dependents
   
    If Err.Number > 0 Then
        Exit Sub
    End If
   
    If Not Application.Intersect(rDependent, Range("B5:K5")) Is Nothing Then
        Call Check_Parts_Running
       
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top