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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Edit: I changed the order of the cases.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.