InputBox with Array of Choices?

[BAlGaInTl]

Ok...

I'm working on some code that requires user input. However, I want to provide them a list of 3-5 choices. I can easily get the code to work with an InputBox, but the macro actually goes and gets information from a DB. So, any mispellings, incorrect case, etc causes it to crash.

So, I've defined an array in my code called aMethod with an array of string values.

I know from reading the help, that inputbox allows the array type (64), but I can't find any examples on how to get it work....

So I have something like

strMethod = Application.Inputbox("Select Method", "Select a Method", 64)

But for the love of me, I can't figure out how to get that to use the aMethod array ...

Can someone give me a clue?

Thanks.

 

[Norie]

Isn't the array part there to allow users to enter more than one value in the inputbox?

I don't think it's meant to allow the user to make a choice from an array of values.

You could display all the values in your array in the text of the inputbox but that might be a bit unsightly.

Have you considered a userform?

You could easily have all the values in your array in a combobox or listbox to allow the user to make there choice.s

 

[MickG]

If you enter the array as a range :- A1:A10. This works.

Dim Ray As Variant
Ray = Application.InputBox(prompt:=" ", Title:="Range Array", Type:=64)
Range("F1").Resize(UBound(Ray)) = Ray

Mick

 

[BAlGaInTl]

Isn't the array part there to allow users to enter more than one value in the inputbox?

I don't think it's meant to allow the user to make a choice from an array of values.

You could display all the values in your array in the text of the inputbox but that might be a bit unsightly.

Have you considered a userform?

You could easily have all the values in your array in a combobox or listbox to allow the user to make there choice.s

Yes, I can do it in a userform. I just thought there might be a cleaner way to do it to keep all my code in the same subroutine. I hate to make a userform for 3 choices....

If you enter the array as a range :- A1:A10. This works.

Dim Ray As Variant
Ray = Application.InputBox(prompt:=" ", Title:="Range Array", Type:=64)
Range("F1").Resize(UBound(Ray)) = Ray

Mick

Where does A1:A10 come in to play there?

This is more of a query that I do from Excel, and not performed on a workbook per se. That being said, in order to use that method, I would have to create a range with the values in it first...

 

[Norie]

What exactly do you want to do?

List the options the user can chose from?

If that's all then you could just try doing that with the text of the inputbox using a sort something like a numbered list.

Kind of primitive but it would work.

 

[BAlGaInTl]

What exactly do you want to do?

List the options the user can chose from?

If that's all then you could just try doing that with the text of the inputbox using a sort something like a numbered list.

Kind of primitive but it would work.

yeah...

I guess I misinterpreted the type 64 inputbox.

I ended up just creating a userform and passing the variable that way. It is the cleanest way I think.