Cancel Button makes a selection rather than cancelling

Question

I'm using the following code behind 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

The ComboBox allows the user to select an email template. When one is selected and OK clicked, the template is opened in Outlook.

This is the code which 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 can I close the form without this first template opening at the same time?

Answer 1

Whilst it is possible to solve the issue by using a global variable, a much neater solution is use the UserForm.Tag property to pass a result back to the main procedure.

Be aware that unloading the UserForm also removes the tag value, so you need to hide the UserForm in the click handlers, use the tag value in the main procedure, and then unload the UserForm.

Select_Email_Template UserForm code:

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

Non-Class Module code:

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

Explanation:

The main idea here is to return, via the Select_Email_Template.Tag property, a -1 when the user clicks Cancel and a valid index (0 to 7 in your example) when the user clicks OK.

The code also redirects ALT+F4, clicking the close box (and its keyboard shortcut equivalent ALT+SPC;C), and any other method of closing the UserForm, to the cancel button's click handler.

I've also taken the liberty of refactoring your code so all the template data is declared once only and in one place only, i.e., in the Templates() function.

I've used a static variable in this function so that the array is only ever initialized once. You could just declare it with Dim and skip the empty check and it would still work just fine.

---

Note: If you are curious about my variable naming convention, it is based on RVBA.

Answer 2

The global variable lstnum is initially 0. Since you are using lstnum as the Select trigger make it -1, to conform to the norm.

Outlook Form To Select Email Template

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

Following the method you are using to return the choice from the userform.

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