The Cancel button makes a selection, not a cancellation

Question

I am using the following code for an Outlook UserForm called Select_Email_Template.

Private Sub UserForm_Initialize()
  With ComboBox1
    .AddItem "Account Amendment Non SC"
    .AddItem "Account Amendment SC Application Received"
    .AddItem "Account Amendment SC"
    .AddItem "Account Creation Non SC"
    .AddItem "Account Creation SC Application Received"
    .AddItem "Account Creation SC"
    .AddItem "Export Function"
    .AddItem "Password Reset"
  End With
End Sub

Private Sub btnOK_Click()
    lstNum = ComboBox1.ListIndex
    Unload Me
End Sub

Private Sub btnCancel_Click()
    Unload Select_Email_Template
End Sub

ComboBox allows the user to select an email template. When one is selected and OKclicked, the template opens in Outlook.

This is the code that opens the templates:

Public lstNum As Long

Public Sub Email_Templates()

    Dim outMail As Outlook.MailItem

    Select_Email_Template.Show

    Select Case lstNum

    ' Following the listbox entries


    Case 0
        Set outMail = CreateItemFromTemplate("TemplatePath\Account Amendment Non SC.oft")

    Case 1
        Set outMail = CreateItemFromTemplate("TemplatePath\Account Amendment SC Application Received.oft")

    Case 2
        Set outMail = CreateItemFromTemplate("TemplatePath\Account Amendment SC.oft")

    Case 3
        Set outMail = CreateItemFromTemplate("TemplatePath\Account Creation Non SC.oft")

    Case 4
        Set outMail = CreateItemFromTemplate("TemplatePath\Account Creation SC Application Received.oft")

    Case 5
        Set outMail = CreateItemFromTemplate("TemplatePath\Account Creation SC.oft")

    Case 6
        Set outMail = CreateItemFromTemplate("TemplatePath\Export Function.oft")

    Case 7
        Set outMail = CreateItemFromTemplate("TemplatePath\Export Function.oft")

    End Select

    ' Use for a specific purpose not randomly
    ' On Error Resume Next

    With outMail
        .Display
    End With

    ' On Error GoTo 0

cleanup:
        Set outMail = Nothing

  End Sub

When the user clicks Cancel, the form closes, but the first template from the list opens in Outlook.

How to close a form without opening this first template at the same time?

Answer 1

, , UserForm.Tag, .

, UserForm , UserForm , UserForm.

Select_Email_Template UserForm:

Private Sub UserForm_Initialize()

  Dim varTemplateName As Variant

  With ComboBox1
    For Each varTemplateName In Templates(NameOnly:=True) ' Templates() also works
      .AddItem varTemplateName
    Next
  End With

End Sub

Private Sub btnOK_Click()
  Me.Tag = Me.ComboBox1.ListIndex
  Me.Hide
End Sub

Private Sub btnCancel_Click()
  Me.Tag = -1
  Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = VBA.VbQueryClose.vbFormControlMenu Then
    Cancel = True
    btnCancel_Click
  End If
End Sub

-:

Public Sub Email_Templates()
  With Select_Email_Template
    .Show
    If .Tag <> -1 Then
      CreateItemFromTemplate(Templates(.Tag, FullPath:=True)).Display ' Templates(.Tag) also works
    End If
  End With
  Unload Select_Email_Template
End Sub

Public Function Templates _
                ( _
                  Optional ByVal plngIndex As Long = -1 _
                , Optional ByVal NameOnly As Boolean = False _
                , Optional ByVal FullPath As Boolean = False _
                ) _
       As Variant

  Const strcTemplatesDir As String = "<TemplatesPath>\"
  Const strcTemplateExtension As String = ".oft"

  Static avarTemplateNames As Variant

  If IsEmpty(avarTemplateNames) Then
    avarTemplateNames = Array _
    ( _
      "Account Amendment Non SC" _
    , "Account Amendment SC Application Received" _
    , "Account Amendment SC" _
    , "Account Creation Non SC" _
    , "Account Creation SC Application Received" _
    , "Account Creation SC" _
    , "Export Function" _
    , "Export Function" _
    )
  End If
  If plngIndex <> -1 Then
    If NameOnly = True And FullPath = False Then
      Templates = avarTemplateNames(plngIndex)
    Else
      Templates = strcTemplatesDir & avarTemplateNames(plngIndex) & strcTemplateExtension
    End If
  Else
    Templates = avarTemplateNames
  End If

End Function

:

- Select_Email_Template.Tag -1, Cancel ( 0 7 ), OK.

ALT + F4, ( ALT + SPC; C) UserForm, .

, , .. Templates().

, . Dim , .

---

. , RVBA.

Answer 2

lstnum 0. lstnum, Select -1, .

Outlook

Case -1
'  -1 is what you want to use if nothing is selected

, , .

Private Sub btnCancel_Click()
    lstNum = -1
    Unload Select_Email_Template
End Sub

Answer 3

@IRHM , . MsgBoxes . . , , , , .

Sub Email_Templates()
Dim ComboBox1
Dim intCount As Integer
Dim intSelectedIndex As Integer
Dim myNum As Integer

'Dim outMail As Outlook.MailItem

Userform1.Show

myNum = Userform1.ComboBox1.ListIndex

If myNum = 0 Then
  Goto Abort
Else

MsgBox ("Back to the main module")

 Select Case myNum

 Case 1
    'Using MsgBox to test and make sure it working
    MsgBox "You selected Account Amendment Non SC - Option 1"
    'Set outMail = CreateItemFromTemplate("TemplatePath\Account Amendment Non SC.oft")

Case 2
    MsgBox "You selected Account Amendment SC Application Received - Option 2"
    'Set outMail = CreateItemFromTemplate("TemplatePath\Account Amendment SC Application Received.oft")

Case 3
    MsgBox "You Selected Account Amendment SC - Option 3"
    'Set outMail = CreateItemFromTemplate("TemplatePath\Account Amendment SC.oft")
End Select
Abort:
 End If
 Unload UserForm1
End Sub

UserForm:

Sub btnCancel_Click()
  MsgBox ("Cancel button selected")
  Unload Me
End Sub

Sub btnOK_Click()
  MsgBox ("OK Selected")
  Me.Hide
End Sub

Sub UserForm_Initialize()
'Put all your AddItems here
  With ComboBox1
    .Clear
    .AddItem "This should exit", 0
    .AddItem "Selection 1", 1
    .AddItem "Selection 2", 2
    .AddItem "Selection 3", 3
  End With
End Sub