InputBox methode

[Aeronav]

Hi,
I tried to build a sub to retrieve some values (numbers, boolean, string)
with the InputBox method using Type:=64,
Sub Test()
Dim tableau() as Variant
tableau = Application.inputBox("Essai", Type:=64)
Debug.Print tableau(2)
End Sub
Debuging the project gives no error
Running the Sub, I write in the InputBox "AAA",25,"BBB",64
As soon as I hit OK I get an error, type incompatible.
First : Is my sub correct ?
If it is, How can I write the elements of the Array in the InputBox?
The Excel Help file does not give an example for this type of entry
Thanks

 

[JLGWhiz]

Your array must contain all the same data types. You cannot mix strings and
numbers. Try enclosing your numbers in quotation marks and see if it then
works.

 

[JLGWhiz]

A little more clarification. When you assign Type:=64, this causes VBA to
edit the entry in the InputBox for array criteria. So, the error message you
are getting is telling you that your entry does not meet the criteria of an
array because you have a wrong data type in the array. By declaring the
array as variant, it will take either a set of strings, or a set of numbers,
or a set of dates, etc. but not a mixture of the data types. Every item in
the array must be the same data type.

 

[Dave Peterson]

As a user, I would find using the application.inputbox not very friendly. I'd
rather have a userform that allowed multiple inputs (with an ok and cancel
button).

But if you wanted, you could prompt the user for a delimited (by comma???)
string and then your code would parse it the way you wanted.

But if you want to use the application.inputbox with type:=64+2 (for all
strings), you could experiment with this:

Sub testme()
Dim myArr As Variant
Dim iCtr As Long
Dim userCancelled As Boolean

Dim resp As Long

myArr = Application.InputBox(prompt:="Three values", Type:=64 + 2, _
Default:=Array("first", "second", "third"))

userCancelled = False
For iCtr = LBound(myArr) To UBound(myArr)
If myArr(iCtr) = False Then
userCancelled = True
End If
MsgBox myArr(iCtr)
Next iCtr

If userCancelled Then
Exit Sub
End If

'more code here

End Sub

=======
And if you want to design your own userform:

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Peter Aiken Articles:
Part I
http://msdn.microsoft.com/library/en-us/dnoffpro01/html/IntroductiontoUserFormsPartI.asp
Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/IntroductiontoUserFormsPartII.asp

 

[Aeronav]

Thanks to both of you.
I use a french version of Excel 2007, but I think that the version is not in
cause
To Dave Peterson
Your Sub works well, but with a strange behaviour :
When run, the inputbox shows the first item of the defaul array : first,
which I overwrite, i.e. with AAA., OK. Immediately the InputBox shows :
second, which I overwrite with BBB, OK. Then : third overwritten by CCC, OK.
Three MsgBoxes appear immediately, successively, presenting AAA, then BBB
and CCC.
So I cannot enter more items than there are in the Default Array. To get
more I can add, for example, 2 empty strings in Dafault : "first", "second",
"third", "", "". Now I can enter 5 items.
I tried also to Redim MyArray(4) but it does not work.
I agree that this is an awful manner to get an Array, but why does Microsoft
offer the Type 64 ?
I can designe simple userforms, so I shall stick to them.

 

[Dave Peterson]

I think that in this case "Strange = Normal".

Using multiple inputboxes and type:=64 seems like a pretty good approach to me.

How would you design application.inputbox using type:=64 to allow me to enter
just two strings as two elements in an array?

My two elements/strings are:
This is the first; and this is still the first, or should I say it's continued?
This is the second--and this is it for the second. I'm done!

 

[Aeronav]

Hi,
To Dave Peterson,
I found something else... During my previous trials to get an Array with
Type:=64, after some erratic entries, I got a warning of error about an
invalid "matricial value". It gave me an idea.
Now here is my new Sub :
Sub Essai()
Dim Tableau As Variant
Tableau = Application.InputBox("Essai", Type:=64)
MsgBox Tableau(1)
MsgBox Tableau(2)
MsgBox Tableau(3)
End Sub
It works if in the InputBox I write a matrix such as : {"AA"."BB"."CC"}
There are curly braces around the values and a full stop between each item.
In this manner I can enter as many items as I want.
The LowerBound of the array is 1, though I did not fix it anywhere. The Sub
works for String entries and numeric entries {10.20.30}. I could not enter
dates in any format.
Good evening
B.Caneau

 

[Dave Peterson]

I have to use the windows list separator (a comma for me).

But as a user, I'd rather see a userform.