Two Conditions Call Macro Private Sub

Srivatsal

New Member
Joined
Dec 4, 2017
Messages
7
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"

NameEmail addressCondition1Condition2
User11@c.comInitiatedCompleted
User12@c.comInitiatedCompleted
User13@c.comInitiatedCompleted

<tbody>
</tbody>
**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

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Dec 4, 2017
Messages
7
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,915
Members
414,110
Latest member
docops

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