vba - 取消按钮进行选择而不是取消

Question

我在名为 Select_Email_Template 的 Outlook 用户窗体后面使用以下代码。

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

组合框允许用户选择电子邮件模板。选择一个模板并单击确定后，该模板将在 Outlook 中打开。

这是打开模板的代码:

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

当用户单击取消时，表单将关闭，但列表中的第一个模板将在 Outlook 中打开。

如何在不同时打开第一个模板的情况下关闭表单？

Answer 1

虽然可以通过使用全局变量来解决该问题，但更简洁的解决方案是使用 UserForm.Tag属性将结果传递回主过程。

请注意，卸载 UserForm 也会删除标记值，因此您需要在点击处理程序中隐藏 UserForm，在主过程中使用标记值，然后然后 卸载用户窗体。

Select_Email_Template用户表单代码:

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，当用户单击确定时为有效索引(示例中为 0 到 7)。

该代码还重定向 ALT+F4，单击关闭框(及其等效的键盘快捷键 ALT+SPC;C)，以及关闭用户窗体的任何其他方法，到取消按钮的单击处理程序。

我还冒昧地重构了您的代码，以便所有模板数据仅在一个位置声明一次，即在 Templates() 中。功能。

我在此函数中使用了静态变量，以便数组仅初始化一次。你可以用 Dim 声明它并跳过空检查，它仍然可以正常工作。

<小时/>

注意:如果您对我的变量命名约定感到好奇，它是基于 RVBA .