excel - 相同的消息框文本取决于多个可能的 InputBox 条目中的任何一个

Question

我想创建一个 msgbox根据 input box 中的特定条目显示文本.
请参阅下面的示例代码，其中将值输入 input box ，“Fantastic”或“Rubbish”，收到不同的信息。

Dim UserAge as String
UserAge = Application.InputBox("Hi, how was your day?", Type:=3)
If InStr(1, UserAge.Text, "Fantastic") > 0 Then #runtime error '424'
    MsgBox "I am glad you are having a good day!"
ElseIf InStr(1, UserAge.Text, "Rubbish") > 0 Then
    MsgBox "I am sorry you are having a bad day!"
End If
                    
End Sub

为了扩展，我还想将代码调整为相同的 msgbox出现输入的多个选项，例如下面。

UserAge = Application.InputBox("Hi, how was your day?", Type:=3)
If InStr(1, UserAge.Text, "Fantastic", "Excellent", "Great") > 0 Then
    MsgBox "I am glad you are having a good day!"
ElseIf InStr(1, UserAge.Text, "Rubbish","Awful") > 0 Then
    MsgBox "I am sorry you are having a bad day!"
End If

Answer 1

区分不同的组以及数字和字符输入
由于 OP 区分

不同团体 属性输入和

明确传递 Type:=3 参数(接受文本 和 数字字符串)到 Application.InputBox()功能，

这激发了我在下面的代码示例中演示如何处理这些单独的规范。
示例调用
不同于 VBA 的 InputBox Application.InputBox()函数允许指定返回数据类型。所以一个 Type:=3 Application.InputBox() 中的参数(即 3 作为 1 数字 + 2 文本的总和)接受文本和数字字符串输入。

见 MS Help Application.InputBox .

Sub ExampleCall()
'Purp: get (case insensitive) UserInput via Application.InputBox and check response
    Dim UserAge As String
    UserAge = Application.InputBox("Hi, how was your day?", Type:=3)
    
    CheckResponse UserAge          ' << call procedure CheckResponse
End Sub

程序 CheckResponse
包括以下步骤 [0]定义 全部 零边界数组中列出的允许属性 keywords .

keywords = Split("super,fantastic,great,rubbish,awful", ",")    

[1]在其中定义每组的最后一个元素编号(组结束编号):

Group = Array(0, 3, 5)

在哪里

[组(0): ----------------- (= 0 )]

Group(1): super .. great (=3rd element)

组(2):垃圾..糟糕(=第 5 个元素)

[2]获取 职位编号 num在 keywords 内, 准确地说是通过 Val()如果是数字输入或通过 Application.Match()在文本输入的情况下。 - 注意如何在两种情况下检查位置结果的有效性。 [3]根据 num 查找结果组号在 keywords 中的位置 [4]显示单个消息框。

Sub CheckResponse(ByVal entry)
'[0]Define valid attribute inputs
    Dim keywords: keywords = Split("super,fantastic,great,rubbish,awful", ",")
'[1]Define positions of last element number within user defined groups 0/1/2:
    Dim Group(): Group = Array(0, 3, 5)
 Stop
'[2]get attribute position within keywords (1-based)
    Dim num As Variant
    If Val(entry) Then      ' a) numeric input
        num = Val(entry)
        If num > Group(UBound(Group)) Then num = 0
    Else                    ' b) characters = textual input
        num = Application.Match(entry, keywords, 0)
        If Not IsNumeric(num) Then num = 0
    End If

'[3]get group number
    Dim GroupNum As Long
    Select Case num
    Case Group(0)
        MsgBox "Input " & entry & " not found!", vbCritical, "Invalid Attribute"
    Case Is <= Group(1)
        GroupNum = 1
    Case Is <= Group(2)
        GroupNum = 2
    End Select
    
'[4]display message
    If GroupNum Then
        MsgBox "**My day was " & _
               keywords(num - 1) & ".**" & String(2, vbNewLine) & _
               "(Identified as " & _
               num - Group(GroupNum - 1) & ". item in " & _
               GroupNum & ". group)", vbInformation, "My Response"
    End If
End Sub

玩得开心 :-)