Two Conditions Call Macro Private Sub

[Srivatsal]

Hi,

Need help I am new pretty new to VBA, created a worksheet it is working for 1 condition but I tried a lot but unable to make it work for the second condition in the same worksheet.

Background: The first code pasted below calls the I need to below code to call a macro when the condition1 cell changes to "Initaited" similarly I want it to call second macro in general module when the condition1 cell changes to "Completed"

[TABLE="class: cms_table_cms_table, width: 271"]
<tbody>[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Email address[/TD]
[TD="align: center"]Condition1[/TD]
[TD="align: center"]Condition2[/TD]
[/TR]
[TR]
[TD="align: center"]User1[/TD]
[TD="align: center"]1@c.com[/TD]
[TD="align: center"]Initiated[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]User1[/TD]
[TD="align: center"]2@c.com[/TD]
[TD="align: center"]Initiated[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]User1[/TD]
[TD="align: center"]3@c.com[/TD]
[TD="align: center"]Initiated[/TD]
[TD="align: center"]Completed
[/TD]
[/TR]
</tbody>[/TABLE]
**Lets assume the colums are "N" for condition1 and column "S" for condition2 in the above case.**

I have written the code below but doesnt seem to work
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
LR = Range("N" & Rows.Count).End(xlUp).Row
'This assumes a Header Row in N1
If Not Intersect(Target, Range("N2:N" & LR)) Is Nothing Then
' If Target.Value = "Initiated" Then
Call Create_Mail_From_List(Target)
' End If
LR = Range("S" & Rows.Count).End(xlUp).Row
'This assumes a Header Row in S1
If Not Intersect(Target, Range("S2:S" & LR)) Is Nothing Then
' If Target.Value = "Initiated" Then
Call Create_Mail_From_List1(Target)
' End If
End If
End If
End Sub
[TABLE="class: cms_table, width: 271"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

This is a duplicate posting.
See previous post here:
https://www.mrexcel.com/forum/excel...et_change-byval-target-range.html#post4963162


I gave you a answer.

The problem is when a certain condition is met you want to call a macro.

But you never gave the name of the Macro you want to call.

I gave you a example like Call Bob Or Call George

The only thing I see here is you have said call:

Call Create_Mail_From_List1(Target)

This cannot be the name of a script.

Show me the script you want to call:

 

[Norie]

Have you tried stepping through the code?

You can do that by setting up a breakpoint on the sub header using F9 and then making changes in the worksheet.

When you make a change you should enter into Debug mode where you can step through the code using F8.

PS I'm pretty sure the 2 if statements shouldn't be nested.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long

    LR = Range("N" & Rows.Count).End(xlUp).Row
    'This assumes a Header Row in N1
    If Not Intersect(Target, Range("N2:N" & LR)) Is Nothing Then
        ' If Target.Value = "Initiated" Then
        Call Create_Mail_From_List(Target)
    End If
    
    LR = Range("S" & Rows.Count).End(xlUp).Row
    'This assumes a Header Row in S1
    If Not Intersect(Target, Range("S2:S" & LR)) Is Nothing Then
        ' If Target.Value = "Initiated" Then
        Call Create_Mail_From_List1(Target)
        ' End If
    End If

End Sub

 

[Srivatsal]

This is a duplicate posting.
See previous post here:
https://www.mrexcel.com/forum/excel...et_change-byval-target-range.html#post4963162


I gave you a answer.

The problem is when a certain condition is met you want to call a macro.

But you never gave the name of the Macro you want to call.

I gave you a example like Call Bob Or Call George

The only thing I see here is you have said call:

Call Create_Mail_From_List1(Target)

This cannot be the name of a script.

Show me the script you want to call:

For some reason I could do anything on the previous thread hence I couldnt reply

Here you go the script it is calling
Option Explicit
Sub Create_Mail_From_List(Target As Range)
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000,
' Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
' Adapted from http://msdn.microsoft.com/en-us/libr...9602(office.11)
'.aspx#odc_office_UseExcelObjectModeltoSendMailPart2_MailingMessagetoEachPerson
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim t, ref, can As Variant
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
t = ActiveCell.Offset(0, -1).Value

ref = ActiveCell.Offset(0, -13).Value
can = ActiveCell.Offset(0, -12).Value

With OutMail
.To = t
.Subject = "PEC - " & Target
.Body = "Hi " & "," & vbNewLine & vbNewLine & "Ref#-" & ref & " candidate name-" & can & vbNewLine & vbNewLine & "This is to confirm that" & _
"PEC has been initiated for the above candidate" & vbNewLine & vbNewLine & "Regards," & vbNewLine & "TSS"
'You can also add files like this:
'.Attachments.Add ("C:\test.txt")
.send 'Or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Next Module is


Option Explicit
Sub Create_Mail_From_List1(Target As Range)
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000,
' Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
' Adapted from http://msdn.microsoft.com/en-us/libr...9602(office.11)
'.aspx#odc_office_UseExcelObjectModeltoSendMailPart2_MailingMessagetoEachPerson
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim t, ref, can As Variant
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
t = ActiveCell.Offset(0, -5).Value

ref = ActiveCell.Offset(0, -18).Value
can = ActiveCell.Offset(0, -17).Value

With OutMail
.To = t
.Subject = "PEC - " & Target
.Body = "Hi " & "," & vbNewLine & vbNewLine & "Ref#-" & ref & " candidate name-" & can & vbNewLine & vbNewLine & "This is to confirm that" & _
"PEC has been initiated for the above candidate" & vbNewLine & vbNewLine & "Regards," & vbNewLine & "TSS"
'You can also add files like this:
'.Attachments.Add ("C:\test.txt")
.send 'Or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

 

[Srivatsal]

I havent tried that let me try and will reply back...Thank you.

 

[My Aswer Is This]

Well this is surely way beyond my Knowledgebase.
I have never used a script like this. I cannot even get Excel to recognize this as a script.

But there are lots of folks here much smarter then me so I'm sure someone else here will jump in here and help you.