VBA - Message based on values in TextBox1

Hi

In my excel userform1 TextBox1, i can have different values in it.

What i'm trying to do is to look inside the textbox and find:
 if i see the value "1". it will give the msgbox " Test1"
if i see the value "2". it will give the msgbox " Test2"
if i see the value "3".  it will give the msgbox " Test3"
if i see the value "1" and  value "2".  it will give the msgbox " Test4"
if i see the value "1" and  value "2" and  and  value "3".  it will give the msgbox " Test5"

In the textbox1, i can have multiple different valus.
Ex:
"30,10,20"
"10"
"20"
"10,20,30" ...

Based on above find criterias, i need the specific message.

Ex:
In i have "30,20"10", since i have the value "1" and "2" and "3", the message would be " Test5"
If i have "30", the message would be: "Test3" ....

right now, i was using:
If InStr(TextBox1.text, "1") then msgbox "Test1"
If InStr(TextBox1.text, "2") then msgbox "Test2"
If InStr(TextBox1.text, "3") then msgbox "Test3"
If InStr(TextBox1.text, "1") <> 0 and InStr(TextBox1.text, "2") <> 0 then msgbox "Test4"
If InStr(TextBox1.text, "1")<> 0  and InStr(TextBox1.text, "2") <> 0  and InStr(TextBox1.text, "3") <> 0 then msgbox "Test5"

Open in new window


is this the best approach or i should use another way?

Thank you for your help.
LVL 11
Wilder1626Asked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
Select Case True
    Case InStr(TextBox1.text, "1")<> 0  and InStr(TextBox1.text, "2") <> 0  and InStr(TextBox1.text, "3") <> 0
        msgbox "Test5"   
    Case InStr(TextBox1.text, "1") <> 0 and InStr(TextBox1.text, "2") <> 0
        msgbox "Test4"
    Case InStr(TextBox1.text, "1") > 0
        msgbox "Test1"
    Case InStr(TextBox1.text, "2") > 0
        msgbox "Test2"
    Case InStr(TextBox1.text, "3") > 0
        msgbox "Test3"
End Select

Open in new window

That way at least it won't evaluate all the statements after one is true.
0
 
Martin LissOlder than dirtCommented:
Edit: I changed the order of the cases.
0
 
Ryan ChongCommented:
since you don't have the common comparison pattern then you got to do the comparison by writing a series of IF statements, or in other case the Select Case statement.
0
 
Wilder1626Author Commented:
Thanks again for your help. I will use the case statement for sure if this will speed up the run time.
0
 
Martin LissOlder than dirtCommented:
It will speed up the run time but unless you execute that part of the code thousands of times you won't notice it. What I should point out, and you may know this already, but with your original code if both '1' and '2' are in the textbox you will get three messages, forTest1, Test2 and Test4, but in any case you're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.