InputBox methode

  • Thread starter Thread starter Aeronav
  • Start date Start date
A

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
 
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.
 
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.
 
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
 
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.
 
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!
 
Back
Top