VBA: Application.InputBox Type Problems

[JTannas]

Hello All,

I am building a macro for collecting information from several different workbooks with similar information, but different formats (eg. product numbers from bids from many different companies).The information can be either part of a cell, the entire cell, or spread across several cells.

The problem is when using an App.Inputbox with both Type 2 (text) and type 64 (array).
When selecting a multi-cell range (should be treated as an array), the inputbox returns it as a string containing the value of the first cell only. When using only a type=2 box and selecting a range, the same error occurs.

Q: How can I get a user input that allows both array selection and manual entry?

Better seen than explained so here's the code...

Sub AppInputBox_Headache()
Debug.Print "Begin AppInputBox_Headache()"
Dim varInput As Variant

varInput = Application.InputBox("Please Select or Type the info.", _
                                Type:=2 + 8 + 64)
Debug.Print TypeName(varInput)
Debug.Print varInput

Debug.Print "End AppInputBox_Headache()"

End Sub

Windows 7, Office 2010

 

[bertie]

The bottom line is you cannot do this:

varInput = Application.InputBox("Please Select or Type the info.", _
                                [COLOR=red]Type:=2 + 8 + 64[/COLOR])

Think about it.
If you set an Application.InputBox to get a numeric value, Type:=1, and a user typed in a character, the user would receive an error that a numeric value is required. So you cannot use multiple types to allow entry of either numeric or character values. It sort of defeats the purpose of the Excel InputBox.

One option is to allow the user to enter:
1. for text
2. for cell/range of cells
And process the users response with an IF statement or Select Case statement.

[COLOR=darkblue]Sub[/COLOR] Test1()
   Debug.Print "Begin AppInputBox_Headache()"
   [COLOR=darkblue]Dim[/COLOR] varInput [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] myChoice [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
 
   [COLOR=green]'============================[/COLOR]
   [COLOR=green]'Prompt the user for input type[/COLOR]
   [COLOR=green]'============================[/COLOR]
   myChoice = Application.InputBox("Enter data type:" & vbCrLf _
                        & "1. Manual" & vbCrLf _
                        & "2. Range", Type:=1)
   [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] myChoice
      [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] = 1
         [COLOR=green]'Type:=2 - text entry[/COLOR]
         varInput = Application.InputBox("Enter Text", Type:=2)
         Debug.Print varInput
      [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] = 2
         [COLOR=green]'Type:=8 - single cell or multiple cell range entry[/COLOR]
         varInput = Application.InputBox("Select Range:", Type:=8)
         [COLOR=green]'See test2[/COLOR]
   [COLOR=darkblue]End[/COLOR] Select
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

If you use type:=8 (range) the user can either select a one cell range or a multiple cell range (this is stored as an array). You have to test for which. I have inserted a Stop command in the test routine below. You can view how the variable stores the value(s) in the Locals Window, click View => locals Window. Press F8 to step through the remanider of the code.

[COLOR=darkblue]Sub[/COLOR] test2()
   [COLOR=darkblue]Dim[/COLOR] varInput [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=green]'==============================================[/COLOR]
   [COLOR=green]'To see how the array stores the range values[/COLOR]
   [COLOR=green]'click View => Locals Window[/COLOR]
   [COLOR=green]'expand the array variable varInput[/COLOR]
   [COLOR=green]'==============================================[/COLOR]
   varInput = Application.InputBox("Plase select your range:", _
                                   Type:=8)
Stop
   [COLOR=green]'multiple cell range selected[/COLOR]
   [COLOR=darkblue]If[/COLOR] IsArray(varInput) [COLOR=darkblue]Then[/COLOR]
      [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](varInput) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](varInput)
         Debug.Print varInput(i, 1)
      [COLOR=darkblue]Next[/COLOR] i
   [COLOR=darkblue]Else[/COLOR]
      [COLOR=green]'single cell range selected[/COLOR]
      Debug.Print varInput
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
   Debug.Print "End AppInputBox_Headache()"
End [COLOR=darkblue]Sub[/COLOR]

Hope this helps,
Bertie

ps If you want to avoid annoying the user with pop-ups you could always create a customised UserForm

 

[JTannas]

Thank you for the reply Bertie, it seems like a good idea to keep the input types seperate in most cases.

Sorry to answer my own question, but here's a workaround that I thought up over the weekend.
By setting the type:=0, the inputbox does not evaluate a range or string passed to it, enabling subroutine level processing. Below is the code for handling the resulting expressions.

Sub AppInputBox_MultiType()
Debug.Print "Begin AppInputBox_MultiType()"
Dim strFormula As String
Dim varInput As Variant

strFormula = Application.InputBox("Please Select or Type the info.", Type:=0)

strFormula = Replace(strFormula, """", "")
strFormula = Application.ConvertFormula(strFormula, xlR1C1, xlA1)
varInput = Evaluate(strFormula)

'Evaluate throws an error#2029 when passed a string (e.g. Evaluate("=test") = Error 2029)
If IsError(varInput) Then varInput = Replace(strFormula, "=", "")
Stop

Debug.Print "End AppInputBox_MultiType()"

End Sub

As for other cases, it is possible to set multiple input types, so long as they're single values rather than arrays. Please see the remarks section of the attached MSDN reference.
MSDN: InputBox Method

Again though, thank you for the reply!
Have a nice week

 

[hershmab]

Another question on Application.Inputbox usage:

A range has been selected in the active worksheet. I now run a SUB to require the User to choose between:

1. leaving the current selection unchanged and continuing the current process
2. changing the selection dynamically to a new RANGE and also continuing the current process
3. cancelling the current process.

1. What should the Application.Inputbox return value be DIMmed as? RANGE, Variant, or what?
2. If I set the TYPE argument to 8 (Range), how do I test the return value for Cancel?