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

Adding sheet with name using VBA

Adding worksheets to Excel is very simple. For example, to add a Worksheet after the active sheet (default unless stated otherwise) this is the default code when u record a macro.

Sub add_worksheet()
Sheets("current sheet").Select

Sheets.Add

Sheets("Sheet9").Select

Sheets("Sheet9").Name = "my sheet"
End sub

But when u want to use this macro as default macro this will not work as the sheet added will be not be "sheet9" every time So possibility here is that we have to give a name to sheet while adding.

This little piece of code solves our purpose:-

Sub add_worksheet()
Sheets.Add.name=("my sheet")
End sub
    
But this will add sheet before active sheet,if we want sheet in the end like default then:-

Sub add_worksheet()
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "my sheet"
End sub

Default syntax for sheet add method is sheets.Add(Before,After,Count,Type).We can either use before or after one time.The only other Variant we can use if desired is the Type Variant. The Type specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. 
If you are inserting a sheet based on an existing template, specify the path to the template (Recording a macro is best for this). The default value is xlWorksheet.