Click a SUBMIT button to activate the coding

[Blanchetdb]

Hi,

I am looking for some help in regards to using a "SUBMIT" button to activate the coding

this is the coding I presently have done:

Dim xRg As Range

Sub Worksheet_Change(ByVal Target As Range)
    Set xRg = Intersect(Range("F12"), Target)
    
    If Not (xRg Is Nothing) Then
        If Target = "yes" Or Target = "oui" Then
            Call Mail_emsg1
        End If
    End If
    
    Set xRg = Intersect(Range("J17"), Target)
    
    If Not (xRg Is Nothing) Then
        If Target = "Granted" Or Target = "Acquise" Then
            Call Mail_emsg2
        End If
    End If
    
    Set xRg = Intersect(Range("J17"), Target)
    
    If Not (xRg Is Nothing) Then
         If Target = "Pending" Or Target = "En Attente" Then
            Call Mail_emsg3
         End If
    End If
    
    Set xRg = Intersect(Range("H4"), Target)
    
    If Not (xRg Is Nothing) Then
         If Target = TargetValue Then
            Call Mail_emsg4
         End If
    End If
    
End Sub
Sub Mail_emsg1()
    Dim pNum As String
    Dim iName As String
    Dim vacancies As String
    Dim nIncumbent As String
    Dim excluded As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    
    pNum = Worksheets("Sheet1").Cells(6, "C").Value
    iName = Worksheets("Sheet1").Cells(10, "E").Value
    vacancie = Worksheets("Sheet1").Cells(12, "K").Value
    nIncumbent = Worksheets("Sheet1").Cells(15, "A").Value
    excluded = Worksheets("Sheet1").Cells(15, "F").Value
    
    
    xMailBody = "Hi Christine" & vbNewLine & vbNewLine & _
                "A 3811 has been submitted for approval that involves a position that is excluded or a person that is presently excluded" & vbNewLine & vbNewLine & _
                "Position number: " & pNum & vbNewLine & _
                "Incumbent's name: " & iName & vbNewLine & vbNewLine & _
                "Is the position presently vacant?: " & vacancie & vbNewLine & _
                "Name of present incumbent: " & nIncumbent & vbNewLine & _
                "Identify incumbent that will be excluded: " & excluded
                On Error Resume Next
    With xOutMail
        .To = "xxxxxxx[EMAIL="xxxxxxx@outlook.ca"]@outlook.ca[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "send by cell value test"
        .Body = xMailBody
        .Display
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Sub Mail_emsg2()
    Dim pNum As String
    Dim iName As String
    Dim vacancies As String
    Dim nIncumbent As String
    Dim excluded As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    
    pNum = Worksheets("Sheet1").Cells(6, "C").Value
    iName = Worksheets("Sheet1").Cells(10, "E").Value
    StaffType = Worksheets("Sheet1").Cells(33, "C").Value
    Secreq = Worksheets("Sheet1").Cells(17, "D").Value
    PRI = Worksheets("Sheet1").Cells(10, "I").Value
    Location = Worksheets("Sheet1").Cells(10, "A").Value
    xMailBody = "Hi Security" & vbNewLine & vbNewLine & _
                "A staffing request has been submitted stating that security has ben obtained" & vbNewLine & _
                "Please reply with confirmation as to the status of the person's security clearance" & vbNewLine & vbNewLine & _
                "Position number: " & pNum & vbNewLine & _
                "Incumbent's name: " & iName & vbNewLine & _
                "PRI: " & PRI & vbNewLine & vbNewLine & _
                "Staffing Type: " & StaffType & vbNewLine & _
                "Security requirement of the position: " & Secreq & vbNewLine & _
                "Location: " & Location
                On Error Resume Next
    With xOutMail
        .To = "[EMAIL="xxxxxxx@outlook.ca"]xxxxxxx@outlook.ca[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "Security clearance status - confirmation request "
        .Body = xMailBody
        .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Sub Mail_emsg3()
    Dim iName As String
    Dim vacancies As String
    Dim nIncumbent As String
    Dim excluded As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    
    
    iName = Worksheets("Sheet1").Cells(10, "E").Value
    StaffType = Worksheets("Sheet1").Cells(33, "C").Value
    Secreq = Worksheets("Sheet1").Cells(17, "D").Value
    PRI = Worksheets("Sheet1").Cells(10, "I").Value
    Location = Worksheets("Sheet1").Cells(10, "A").Value
    xMailBody = "Hi," & vbNewLine & vbNewLine & _
                "A staffing request has been submitted stating that security is Pending" & vbNewLine & _
                "Please use the appropriate link to access the desired security clearance" & vbNewLine & vbNewLine & _
                "Incumbent's name: " & iName & vbNewLine & _
                "PRI: " & PRI & vbNewLine & vbNewLine & _
                "Staffing Type: " & StaffType & vbNewLine & _
                "Security requirement of the position: " & Secreq & vbNewLine & _
                "Location: " & Location & vbNewLine & vbNewLine & _
                "Security Level - Reliability: [URL]http://publiservice.tbs-sct.gc.ca/tbsf-fsct/330-23-eng.asp[/URL] " & vbNewLine & _
                "Security Level - Secret: [URL]http://publiservice.tbs-sct.gc.ca/tbsf-fsct/330-60-eng.asp[/URL]"
                On Error Resume Next
    With xOutMail
        .To = "[EMAIL="xxxxxxx@outlook.ca"]xxxxxxx@outlook.ca[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "Security Clearance Required - link to forms "
        .Body = xMailBody
        .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Sub Mail_emsg4()
    Dim EffStart As String
    Dim EffEnd As String
    Dim ClassReq As String
    Dim iName As String
    Dim pNum As String
    Dim Posrep As String
    Dim Bran As String
    Dim Sect As String
    Dim Reg As String
    Dim nIncumbent As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    
    
    EffStart = Worksheets("Sheet1").Cells(26, "K").Value
    EffEnd = Worksheets("Sheet1").Cells(27, "K").Value
    ClassReq = Worksheets("Sheet1").Cells(27, "D").Value
    iName = Worksheets("Sheet1").Cells(10, "E").Value
    pNum = Worksheets("Sheet1").Cells(6, "C").Value
    Posrep = Worksheets("Sheet1").Cells(26, "G").Value
    Bran = Worksheets("Sheet1").Cells(8, "A").Value
    Sect = Worksheets("Sheet1").Cells(8, "E").Value
    Reg = Worksheets("Sheet1").Cells(8, "I").Value
    Location = Worksheets("Sheet1").Cells(10, "A").Value
    
    xMailBody = "To Org. & Class.," & vbNewLine & vbNewLine & _
                "A Classification request has been submitted" & vbNewLine & _
                "Please refer to the information provided below" & vbNewLine & vbNewLine & _
                "Classification action requested: " & ClassReq & vbNewLine & vbNewLine & _
                "Incumbent's name (if applicable): " & iName & vbNewLine & _
                "Position number (if applicable): " & pNum & vbNewLine & _
                "Position number reports to: " & Posrep & vbNewLine & vbNewLine & _
                "Effective Start Date: " & EffStart & vbNewLine & _
                "End Date: " & EffEnd & vbNewLine & vbNewLine & _
                "Branch: " & Bran & vbNewLine & _
                "Section: " & Sect & vbNewLine & _
                "Region: " & Reg & vbNewLine & _
                "Location: " & Location
                On Error Resume Next
    With xOutMail
        .To = "xxxxxx[EMAIL="xxxxxx@outlook.ca"]@outlook.ca[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "Classification Request"
        .Body = xMailBody
        .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

What I would like to happen is that once the client has completed the form, they click the SUBMIT button at the bottom of the form and it runs the coding on the sheet

 

[Blanchetdb]

I can't get it to work using the checkbox but I replaced the functionality to insert an X and it now works

thank you for your help

 

[Joe4]

I can't get it to work using the checkbox but I replaced the functionality to insert an X and it now works

thank you for your help

You are welcome.

Yes, depending on the kind of object you are using, placing the checkbox in the cell doesn't necessarily bind the element to the cell.
The point of the MsgBox code was to see if it was doing that or not.

Glad you got it working.