Excel VBA: Input Box Text with multiple criteria

Question

I want to create a text box that will store the user's input in the variable "UserChoice". The code for this is as follows:

Dim UserChoice As String
    UserChoice= Application.InputBox("Enter Type (A, B, C):  ", "Input Box Text", Type:=2)

The options the user can type are "A", "B", and "C". Depending on the user's input, subsequent code will execute using If Then statements.

Is there a way for the user to type two or more of the options and have both of the related box of codes execute?

For example, if the user enters "A, B", is it possible to run both If UserChoice = "A" and If UserChoice = "B"?

Thank you

Of course - just include that in your Case statement or add it as another facet of your If statements. — dwirony
– dwirony, Commented Aug 16, 2018 at 17:14
Jeeped looks to have provided a solution below - using InStr to determine if the letter you're looking for is there. — dwirony
– dwirony, Commented Aug 16, 2018 at 17:18
@dwirony, I avoided the use of Select Case because you would have to split UserChoice and loop through each element of the resulting array. Experience has told me that you cannot rely on user input to type a static delimiter properly. Could be any of <space>, <comma-space>, <semi-colon-space> or something else. — user4039065
– user4039065, Commented Aug 16, 2018 at 17:24

user4039065 · Accepted Answer · 2018-08-16 17:17:05Z

3

Test for any of A, B or C first then execute code by testing for each.

Dim UserChoice As String
UserChoice= ucase(Application.InputBox("Enter Type (A, B, C):  ", "Input Box Text", Type:=2))

if not iserror(application.match(left(UserChoice, 1), array("A", "B", "C"), 0)) then
    if cbool(instr(1, UserChoice, "A", vbtextcompare)) then
        'A is found, run code
    end if
    if cbool(instr(1, UserChoice, "B", vbtextcompare)) then
        'B is found, run code
    end if
    if cbool(instr(1, UserChoice, "C", vbtextcompare)) then
        'C is found, run code
    end if
end if

answered Aug 16, 2018 at 17:17

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

user10171431 Over a year ago

Hi there, the code works for A, B, C. However, it doesn't work if I replace them for ABC, BCD, CDE. What should I do to fix it?

user4039065 Over a year ago

It will work for ABC. It will work for the B & C of BCD. It will work for the C of CDE. You never specified that you wanted D, E or even F conditions but those should be very obvious modifications.

Collectives™ on Stack Overflow

Excel VBA: Input Box Text with multiple criteria

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related