Input box method

There are many times in Excel VBA that we are required to gather information from a user. Probably the most frequent method of doing is via a message box, and using if condition for predetermined inputs but what if we want to insert text,select range or sheets.in that case we can use input box

The syntax for the InputBox Function is;
InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

this can be used to provide text to macro as an input however if want to select type of input data then we will precede the InputBox Function with "Application" we get an InputBox Method that will allow us to specify the type of info that we can collect. Its Syntax is;

Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)
As you can see, the Prompt, Title and Default are the same as in the InputBox Function. However, it is the last argument "Type" that allows us to specify the type of data we are going to collect. These are as shown below;

Type:=0 A formula
Type:=1 A number
Type:=2 Text (a string)
Type:=4 A logical value (True or False)
Type:=8 A cell reference, as a Range object
Type:=16 An error value, such as #N/A
Type:=64 An array of values

Eg:-

Set rng = Application.InputBox(Prompt:="Please select a range or cell.", _ 

     Title:="RANGE selection",Type:=8)

rng.select

To select sheet we will use Parent property

Set sh= Application.InputBox(Prompt:="Please select a range or cell.", _ 
     Title:="RANGE selection",Type:=8).parent
sh.select