Excel VBA InputBox

Input Box

The InputBox function in VBA is used to prompt the users to enter values. The user can click either the OK button or can choose the CANCEL button. If the user clicks the OK button (After entering the values) or presses ENTER on the keyboard, this function will return the specific text in the text box. Else, if the user presses the Cancel button or the box is empty, by default, the InputBox function will return an empty string ("").

Syntax

InputBox(prompt[,title] [,default] [,xpos] [,ypos] [,helpfile,context])

Parameter

• Prompt (required) – This parameter represents a string value shown as a message in the dialog box. The maximum number of characters that can be entered is approximately 1024. If the specified message is more than a line, then the lines can be separated using a carriage return character (Chr(13)) or a linefeed character (Chr(10)) between each line.
• Title (optional) – This parameter represents a String expression shown in the title bar of the dialog box. If you don’t specify anything, by default it will show the name of the application.
• Default (optional) – It represents a default text that the user would like to be displayed.
• XPos (optional) – This parameter represents the position of the X-axis (the distance from the left side of the screen horizontally). If XPos is not specified, by default, the input box is horizontally centered.
• YPos (optional) – This parameter represents the position of the Y-axis (the distance from the left side of the screen vertically). If YPos is not specified, by default, the input box is vertically centered.
• Helpfile (optional) – This parameter represents a string expression that identifies the helpfile to provide context-sensitive Help for the dialog box.
• Context (optional) - This parameter represents a numeric expression that defines the Help context number assigned by the Help author to the appropriate Help topic. If the context is specified, helpfile should also be specified.

Example 1

Sub InputBox_Exercise()
 'prompt a box so the users can enter values
 IB = InputBox("Enter your name here!", "Identity", "John Smith")
 'printing the value at Range A1
 Range("A1") = IB
 End Sub 

Output

Press F5 to run the VBA code.

Type the text in the prompt box and click on ok.

You will notice, the name has been displayed in the excel sheet at A1 cell.

Example 2: Write a macro demonstrating the use of Inputbox and MessageBox collectively.

Sub Session_Msgbox_InputBox()
 Dim CR As Byte
 CR = ActiveCell.Row
 col = 2
 'taking the name from the user
 IB = InputBox("Enter your name here!", "Identity", "John Smith")
 'passing the name in the msgbox and asking the user 
 'whether he wants to add 100 or not
 Add_Sales = MsgBox("Hello " + IB + "! Do you want to add 100 to the current sales?", vbYesNo)
     'if user press No, then we will terminate the program
     If Range("A" & CR).Value = Empty Then Exit Sub
     'if user press yes then we will add 100
     If Add_Sales = vbYes Then
         Set rng = Range("D2:D12") 
         For Each cell In rng
             Range("E" & col) = cell.Value + 100
             col = col + 1
             'MsgBox "100 has been added to the current row's sales amount"
         Next
     End If
 End Sub 

Output

Press F5 to run the output. The InputBox will pop up as shown below.

Type your name and click on ok. The MsgBox will pop up as shown below.

Click on yes. You will notice, the values have been displayed in the excel sheet.