add 3 subs on one sheet

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
405
Office Version
  1. 365
Platform
  1. Windows
i want to add 3 subs to one page of which 2 different actions will do a message box email
this is how i did it but it isnt working
what am i doing wrong?

macro1 worked on its own so did macro2
macro2 is broken to begin with
macro3 worked on its own

Private Sub Worksheet_Change(ByVal Target As Range)
Macro1 Target 'event runs when cell in Column E or column F is changed
Macro2 Target 'event runs when cell in Column K is changed
Macro3 Target 'event runs when cell in Column B is changed
End Sub


Private Sub Macro1(ByVal Target As Range)

If Target.Cells.Count = 1 Then


If Cells(Target.Row, "A").Value <> "" And Cells(Target.Row, "E").Value = "_Approvals Missing" And Cells(Target.Row, "F").Value <> "" Or _
Cells(Target.Row, "F").Value <> "" And Cells(Target.Row, "E").Value = "_Approvals Missing" Then

result = MsgBox("pressing OK will send email to notify", vbOKOnly + vbExclamation, "Missing Approval")

If result = vbOK Then

Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)

newmsg.Recipients.Add ("mail@mail.org") ' Add Recipients
newmsg.Subject = Cells(Target.Row, "A").Value & "Missing Approval" ' Add Subject
newmsg.Body = "Missing Approval" & vbCrLf & "" & _
"Please get approval for " & _
Cells(Target.Row, "A").Value & _
" for Missing Class/Membership: " & _
Cells(Target.Row, "F").Value ' Email Body
newmsg.Display 'Display Email
newmsg.Send 'Send Email

MsgBox "Outlook message sent", , "Outlook message sent" ' Confirm Sent Email


End If
End If
End If


End Sub

Private Sub Macro2(ByVal Target As Range)

If Target.Cells.Count = 1 Then


If Cells(Target.Row, "A").Value <> "" And Cells(Target.Row, "K").Value = "x" And Cells(Target.Row, "M").Value <> "" Then

result = MsgBox("pressing OK will send email to notify", vbOKOnly + vbExclamation, "Missing Approval")

If result = vbOK Then

Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)

newmsg.Recipients.Add ("mail@mail.org") ' Add Recipients
newmsg.Subject = Cells(Target.Row, "A").Value & "Missing Approval" ' Add Subject
newmsg.Body = "Missing Approval" & vbCrLf & "" & _
"Please get approval for " & _
Cells(Target.Row, "A").Value & _
Cells(Target.Row, "E").Value & _
Cells(Target.Row, "M").Value ' Email Body
newmsg.Display 'Display Email
newmsg.Send 'Send Email

MsgBox "Outlook message sent", , "Outlook message sent" ' Confirm Sent Email


End If
End If
End If
End Sub

Private Sub Macro2(ByVal Target As Range)

Dim svcMonth As Date
Dim bDate As Date
Dim sAge As Long
svcMonth = Cells(Target.Row, "B")
If Target.Column = 4 And Target.Cells.Count = 1 Then 'Is the user changing one cell in column 4?
If Target.Value = "OTPS Phone Serv" Or Target.Value = "OTPS Internet" Or Target.Value = "OTPS CLOTHING" Or Target.Value = "OTPS Utilities" Then 'Check if the change is questionable
bDate = Cells(Target.Row, "P")
sAge = DateDiff("yyyy", bDate, svcMonth)
If sAge < 18 Then
Cells(Target.Row, "M") = "Special Approval Needed"
End If
End If
End If
End Sub
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Not sure I understand what you are doing. Excel will allow only one of a type of event code per sheet code module. i.e. one Worksheet_Change, one Worksheet_SelctionChange, etc. However you can use one event code to call several other codes from the public code modules.
Example:
This would go in the sheet code module:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("E:F")) Is Nothing Then
            Macro1 'Macro to do Email
        ElseIf Not Intersect(Target, Range("K:K")) Is Nothing Then
            Macro2 'Macro to do Email
        ElseIf Not Intersect(Target, Range("B:B")) Is Nothing Then
            Macro3 'Macro to do Email
        End If
End Sub
Then in code module1 you would have
Code:
Sub Macro1()
    'send email
End sub

Sub Macro2()
    'send email
End Sub

Sub Macro3()
    'Send Email
End Sub

These are simplified examples. Your actual code would need to include the criteria for determining if the email need to be sent,and any other peculiarities that might apply per per change. But this is the way to handle variations in change events using one event code.
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
405
Office Version
  1. 365
Platform
  1. Windows
so to clarify one module explains all the 3 macros
but then where do i put all the code for each specific sub?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Then in code module1 you would have

You must have missed this between the codes. Called procedures need to be in the public modules, sometimes referred to as standard modules. They are the numbered code modules that are inserted in the vb editor. Code entered into those modules can be called from any other code module such as Sheet, ThisWorkbook and UserForms. But the reverse is not true. Private subs are not globally accessable. If you do not currently have a Module1 shown in your Project pane of the vb editor, then on the editor tool bar, click 'Insert>Module' and Module1 will activate in the editor pane.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,402
Messages
5,641,931
Members
417,247
Latest member
Chitaah

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