CODEWITHSUNDEEP
excelvba
CleanRider
CleanRider

Reputation: 149

Same message box text depending on any of multiple possible InputBox entries

I want to create a msgbox that displays text depending on a specific entries in an input box.

See example code below where value entered into the input box, "Fantastic" or "Rubbish", gets different messages.

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

To expand, I would also like to tweak the code so the same msgbox appears for multiple options entered e.g below.

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

Upvotes: 1

Views: 90

Answers (3)

T.M.
T.M.

Reputation: 9948

Distinguish different groups as well as numeric vs. character input

As OP distinguishes between

  • different groups of attribute inputs and
  • passes explicitly a Type:=3 argument (accepting textual and numeric strings) to the Application.InputBox() function,

this stimulated me to demonstrate how to handle these individual specifications in the following code example.

Example call

Differently from VBA's InputBox the Application.InputBox() function allows to specify a return data type. So a Type:=3 argument (i.e. 3 as sum of 1-number + 2-text) in Application.InputBox() accepts both textual and numeric string inputs.

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

Procedure CheckResponse

Comprises the following steps

[0] Define all allowed attributes listed in a zero-bound array keywords.

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

[1] Define therein the last element number of each group (group end number):

Group = Array(0, 3, 5)

where

  • [Group(0): ----------------- (= 0 )]
  • Group(1): super .. great (=3rd element)
  • Group(2): rubbish..awful(=5th element)

[2] get the position number num within keywords, to be precise via Val() in case of numeric inputs or via Application.Match() in case of textual inputs. - Note how to check the validity of the position results in both cases.

[3] find the resulting group number based on the num position in keywords

[4] display the individual message box.

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

Have fun :-)

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33672

Instr can be compared with a single text, you need to change your or statement as follows:

If InStr(1, UserAge, "Fantastic") > 0 Or InStr(1, UserAge, "Fantastic") > 0 Or InStr(1, UserAge, "Fantastic") > 0 Then

However, if you are looking for the extact phrase you entered above, you can use Select Case :

Select Case UserAge
    Case "Fantastic", "Fantastic", "Great"
        ' do something 1
        
    Case "Rubbish", "Awful"
        ' do something 2
        
End Select

Upvotes: 1

VBasic2008
VBasic2008

Reputation: 54777

If Statement Practice feat. Application.InputBox

Option Explicit

Sub exSimple()

    Dim UserAge As Variant
    UserAge = Application.InputBox("Hi, how was your day?", Type:=2)
    
    If UserAge = False Then
        MsgBox "You canceled."
    ElseIf InStr(1, UserAge, "Fantastic", vbTextCompare) > 0 Then
        MsgBox "I am glad you are having a good day!"
    ElseIf InStr(1, UserAge, "Rubbish", vbTextCompare) > 0 Then
        MsgBox "I am sorry you are having a bad day!"
    'Else
        ' Non of the above.
    End If

End Sub


Sub exAdvanced()

    Dim UserAge As Variant
    UserAge = Application.InputBox("Hi, how was your day?", Type:=2)
    
    If UserAge = False Then
        MsgBox "You canceled."
    ElseIf InStr(1, UserAge, "Rubbish", vbTextCompare) > 0 Then
            MsgBox "I am sorry you are having a bad day!"
    Else
        Dim arr As Variant: arr = Array("Fantastic", "Excellent", "Great")
        Dim n As Long
        For n = LBound(arr) To UBound(arr)
            If InStr(1, UserAge, arr(n), vbTextCompare) > 0 Then
                Exit For
            End If
        Next n
        If n <= UBound(arr) Then
            MsgBox "I am glad you are having a good day!"
        'Else
            'none of the above
        End If
    End If

End Sub

Upvotes: 0

Related Questions