Application.InputBox

If you want to obtain information from the user you can use the Excel's InputBox Function.This function is not to be confused with the built-in VBA.InputBox function.There are two advantages that this function has over the VBA function:

You can specify the datatype that is returned.

You can detect when the Cancel key is pressed. If the Cancel button is pressed False is returned.

If you assign the return value to a Variant datatype you can easily test if this value is False.

If you just type InputBox without the Application prefix you will be using the VBA function and not the Excel one.The value returned from the VBA function is always a string.

Example 2 - Formula

Example 3 - Numbers

This will only accept numbers

vReturn = Application.InputBox("InputBox Prompt", , , , , , , 1)

Example 4 - Cell Reference

The user can point to cells with the mouse or type in a range directly.The default value that is displayed is the address of the current selection.Because this is an object you must use the Set statement to assign the range object to the returned object.If the user clicks the Cancel button then False is returned which means the Set statement will fail generating an error.Is Cancel is not pressed then the internal type checking of the InputBox function will guarantee a valid Range object.The On Error Resume Next line means that any run-time error is ignored.