completely flummoxed by the InputBox

[johnnymac]

I have code to Copy and Paste columns of data based on a value in one cell. The user is to input the range of values for that cell, so I have the following lines


lower_limit = InputBox("lower OD limit")
upper_limit = InputBox("upper OD limit")

However, whatever value is in the cell will give True to < upper_limit and False to > lower_limit
MsgBox will show these limits as being assigned correctly, and they are declared as Single but do not seem to be being read as numbers/

If I set the values via

lower_limit = 0.15
upper_limit = 0.6

everything runs fine. Any clue what I have missed, or misused in the InputBox ?

[Norie]

The values returned by an input box are strings but they should convert to numeric if assigned to variables that are declared with a numeric data type.

How are you comparing the values from the input boxes to the values in the cells?

Are the values in the cells numeric?

PS If the user is entering column numbers you should probably use Long (integer) rather than Single (decimal).

[johnnymac]

The values in the cells are numeric and all the rest of the code works with the values assigned in the code. I thought I had found the solution, adding all the parameters to the InputBox and ending with a 1 (numeric), but I keep getting the message that the InputBox function has the wrong number of parameters, though they match what I see in Microsoft's help file.

OK...I have drifted into the twilight zone. My attempt to reply was blocked because it was deemed to contain HTML code ???

[johnnymac]

When I finally got an acceptable number of parameters
lower_limit = InputBox("lower OD limit", , , , , , 1)
I got the error message that it was an invalid procedure call, which seems reasonable since it seems like there are too few parameters. I will have to check the other parameters.

[johnnymac]

When I include 8 arguments (as per Microsoft Excel VBA help) I get this message

Compile error:
Wrong number of arguments or invalid property assignment

If I use one less argument and write lower_limit = InputBox("lower OD limit", "low", 0.15, , , , 1) I get the following

Run-time error '5':
Invalid procedure call or argument

I get the feeling I am missing something obvious.

[Norie]

I think you might be looking at the help for the wrong (type of?) input box.

In Excel VBA there are 2 input boxes, there's the standard VBA InputBox and the Excel specific Application.InputBox.

The former takes 5 arguments - Prompt, Title, DefaultResponse, Xpos and YPos. and returns a String value

The latter takes 8 arguments - Prompt , Title , Default , Left , Top , HelpFile , HelpContextID , Type and returns a Variant.

If you just want the user to enter the no of a column I would suggest using the former, if you want them to actually select the columns via the input box I would suggest the latter.

[johnnymac]

That was exactly right. I was confused by the InputBox function and the InputBox method (where I was reading the help)

Two solutions :

Continue with the InputBox function and multiply the values by 1 before entering into the inequalities

OR

Use Application.InputBox("prompt", Type:=1)

In either case I get numerical values.

Thanks for your help. I will mark this as solved.