add 3 subs on one sheet

[rjmdc]

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

 

[JLGWhiz]

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:

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

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]

so to clarify one module explains all the 3 macros
but then where do i put all the code for each specific sub?

 

[JLGWhiz]

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.