How to Use the VBA Input Box with Buttons in Excel – 2 Examples

Introduction to the Input Box in Excel

The Input Box is a dialog box that takes input from the users. It has two buttons: OK and Cancel.

Syntax of the Input Box in Excel

InputBox( Prompt, [Title], [Default], [XPos], [YPos], [HelpFile], [Context] )

How to Launch VBA Editor in Excel 

To access the Microsoft Visual Basic window, go to the Developer tab and click Visual Basic. You can also press Alt+F11. 

Go to the Insert tab and click Module.

Opening User Form

• In the Microsoft Visual Basic window, click Insert and select Userform.
• The Userform will open with a Toolbox.

Example 1 – Enter Data Using the Input Box in Excel

1.1. Enter a String to a Cell

Enter a String using an Input Box.

• Open the Microsoft Visual Basic code Module window.
• Use following code in the Module.

Sub input_box_string()
    'Declare a variable to store the row number
    Dim iRow As Long
    'Count the number of non-blank cells in the range B5:B12
    iRow = WorksheetFunction.CountA(Range("B5:B12"))
    'Select the cell in column B and the next empty row
    Range("B" & iRow + 5).Select
    'Prompt the user to enter their name
    ActiveCell.Value = InputBox("Enter your name?", "Enter Name")
End Sub

iRow = WorksheetFunction.CountA(Range("B5:B12"))
Range("B" & iRow + 5).Select
ActiveCell.Value = InputBox("Enter your name?", "Enter Name")

• Run the code pressing F5 key or clicking Run.
• An Input Box will be displayed. Enter a name and it will be stored in the selected cell.

___________________________________________________________________________________________________________________________________________________________

1.2. Accept Only Numbers in the Input Box

Create an Input Box that only allows Numbers. Take the inputs and store them in the excel sheet.

• Enter the code in the Module.

Sub Input_box_numbers()
    'Declare a variable to store the user's response
    Dim revenueEarned As Double
    'Clear the contents of cell E5 on the active worksheet
    ActiveSheet.Range("E5").Clear
    'Prompt the user to enter a number
    revenueEarned = Application.InputBox("Enter revenue earned", _
    "Numbers Only", "Only numbers accepted", , , , , 1)
    'If the user enters a valid number
    'store it in cell E5 on the active worksheet
    If revenueEarned <> False Then
        ActiveSheet.Range("E5") = revenueEarned
    End If
    'Display a message box showing the user's input
    MsgBox revenueEarned
End Sub

revenueEarned = Application.InputBox("Enter revenue earned", _
    "Numbers Only", "Only numbers accepted", , , , , 1)
 If revenueEarned <> False Then
        ActiveSheet.Range("E5") = revenueEarned
    End If

 1.3. Enter the Date Using an Input Box

• Go to the Module window and use the following code.

Sub Input_box_date()
Dim strDate As String
Dim nextRow As Long
'Find the next available row in column E
'to store the input date
nextRow = Range("D" & Rows.Count).End(xlUp).Row + 1
'Prompt the user to enter a date
strDate = InputBox("Enter date")
'Check if the input date is valid
If IsDate(strDate) Then
    'If valid, store the date in the next available
    'row in column E and format it as "dd/mm/yyyy"
    Range("D" & nextRow).Value = Format(CDate(strDate), _
    "dd/mm/yyyy")
Else
    'If not valid, display an error message
    MsgBox "Invalid date"
End If
End Sub

nextRow = Range("D" & Rows.Count).End(xlUp).Row + 1

If IsDate(strDate) Then
Range("D" & nextRow).Value = Format(CDate(strDate), _
    "dd/mm/yyyy")

• Run the code and enter a date in the Input Box.

1.4. Using a Formula in the Input Box

Sum the total revenue using the formula.

• Enter the code below in the Module.

Sub Input_box_formula()
    'Declare a variable to store the user's formula input
    Dim formulaInput As String
    'Prompt the user to enter a formula and
    'store the input in the variable
    formulaInput = InputBox("Enter formula:")
    'Check if the user entered a formula
    If formulaInput <> "" Then
        'If a formula was entered,
        'insert it into the active cell
        ActiveCell.Formula = formulaInput
    End If
End Sub

 If formulaInput <> "" Then
ActiveCell.Formula = formulaInput
    End If

• Enter a formula in the Input box to see the result.

1.5. Insert a Cell Reference in an Input Box

Enter a cell reference in the Input Box and change the color of the entered cells.

• Launch Visual Basic
• Enter the following code in the Module.

Sub InputBox_Cell_Reference()
    Dim rng As Range
    Dim rngAddress As String
    On Error Resume Next ' Ignore errors and continue execution
    ' Show an input box for the user to select a range
    Set rng = Application.InputBox(prompt:="Select range to format", _
    Title:="Cell Reference", Type:=8)
    If rng Is Nothing Then Exit Sub ' User clicked Cancel or Esc
    rng.Interior.Color = vbYellow ' Set the interior color of
    'the selected range to yellow
End Sub

Set rng = Application.InputBox(prompt:="Select range to format", _
    Title:="Cell Reference", Type:=8)
 If rng Is Nothing Then Exit Sub ' User clicked Cancel or Esc 
  rng.Interior.Color = vbYellow

• Run the code and enter a range in the Input Box.
• The range is displayed in yellow.

Read More: Excel VBA: Create InputBox with Multiple Inputs

Example 2 – Calculate the Area of a Triangle Using the Input Box in Excel

Take the value of the base and height in the Input Box to calculate the area of a triangle.

• Enter the following code in the Module.

Sub TriArea()
   'Declare variables
   Dim Base As Double
   Dim Height As Double
   Dim TriAreaResult As Double
   Dim OutputCell As Range
   ' Get the base and height of the triangle from the user
   Base = InputBox("Enter Base ", "Enter a Number")
   Height = InputBox("Enter Height", "Enter a Number")
   ' Calculate the area of the triangle
   TriAreaResult = 0.5 * Base * Height
   ' Set the output cell to the next available row
   'in column D starting from D5
   Set OutputCell = Range("D5").End(xlUp).Offset(1, 0)
   ' Output the result to the output cell
   OutputCell.Value = TriAreaResult
End Sub

  Base = InputBox("Enter Base ", "Enter a Number")
   Height = InputBox("Enter Height", "Enter a Number")
   TriAreaResult = 0.5 * Base * Height
   'in column D starting from D5
   Set OutputCell = Range("D5").End(xlUp).Offset(1, 0)
   OutputCell.Value = TriAreaResult

• Run the code and enter the values of base and height to calculate the area of the triangle.

You will get the result in the selected cell.

Read More: How to Use Excel VBA InputBox with Example

How to Check If the Cancel Button was clicked in the Input Box with Excel VBA

Use a code to show “You pressed cancel”.

If the Input Box is left blank, it will display “Nothing is entered”.

• Launch the Microsoft Visual Basic.
• Enter the following code in the Module.

Sub Input_box_check_for_cancel()
' Declare a variable named "response" as a Variant data type.
Dim response As Variant
' Display an InputBox dialog that asks the user to enter a name
response = InputBox("Enter Name")
' Check if the "response" variable points to null
If StrPtr(response) = 0 Then
    ' If the user has pressed the Cancel button,
    'display a message box that says "You pressed cancel."
    MsgBox "You pressed cancel"
' Check if the "response" variable is an empty string.
ElseIf response = "" Then
    ' If the user has not entered anything,
    'display a message box that says "Nothing is entered."
    MsgBox "Nothing is entered"
Else
    ' If the user has entered a name,
    'display a message box that shows the user's input.
    MsgBox response
End If
End Sub

• Run the code and leave the Input Box blank.
• A MsgBox will display “Nothing is entered”.

Read More: How to Handle VBA InputBox Cancel Button in Excel

InputBox vs Application.InputBox

The main difference between the two methods is that the Input Box Method allows data validation. It has an additional argument called “Type” which specifies the data type to be entered. If Cancel is clicked, the Input Box returns False, whereas the Input Box Function returns an empty text string.

How to Create a VBA Input Box That Takes Multiple Inputs in Excel

Create a UserForm and use it as an Input Box.

• Open the Microsoft Visual Basic window.
• Insert a UserForm.
• Drag Labels, Text Boxes, and Command Buttons from the Toolbox and drop them in the UserForm.
• Change the captions of the Labels and the Command Buttons.
• Resize and adjust their positions.

• Double-click OK and Cancel to enter the following codes.

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Multi Input")
Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row + 1
ws.Range("B" & LastRow).Value = UserForm1.TextBox1.Value
ws.Range("C" & LastRow).Value = UserForm1.TextBox2.Value
ws.Range("D" & LastRow).Value = UserForm1.TextBox3.Value
End Sub
Private Sub CommandButton2_click()
Unload Me
End Sub

LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row + 1
ws.Range("B" & LastRow).Value = UserForm1.TextBox1.Value
ws.Range("C" & LastRow).Value = UserForm1.TextBox2.Value
ws.Range("D" & LastRow).Value = UserForm1.TextBox3.Value

• Run the code. It will open a UserForm.
• Enter data in the three Text boxes and click OK.
• Data will be stored in the selected cells.

Read More: Excel VBA InputBox with Multiple Lines

How to Create a VBA Input Box with Customized Buttons in Excel

Create a VBA Input Box with customized buttons.

• Insert a UserForm.
• Add a Text Box and Command Buttons.
• Change the captions of the Command Buttons .

• Enter the code for each Command Buttons. Double-click each Command Button and use the following code.

Private Sub CommandButton1_Click()
Dim Ws As Worksheet
Set Ws = ThisWorkbook.Worksheets("Custom Buttons")
Dim LastRow As Long
LastRow = Ws.Cells(Ws.Rows.Count, "B").End(xlUp).Row + 1
Ws.Range("B" & LastRow).Value = UserForm1.TextBox1.Value
End Sub
Private Sub CommandButton2_Click()
Dim Ws As Worksheet
Set Ws = ThisWorkbook.Worksheets("Custom Buttons")
Dim LastRow As Long
LastRow = Ws.Cells(Ws.Rows.Count, "C").End(xlUp).Row + 1
Ws.Range("C" & LastRow).Value = UserForm1.TextBox1.Value
End Sub
Private Sub CommandButton3_Click()
Dim Ws As Worksheet
Set Ws = ThisWorkbook.Worksheets("Custom Buttons")
Dim LastRow As Long
LastRow = Ws.Cells(Ws.Rows.Count, "D").End(xlUp).Row + 1
Ws.Range("D" & LastRow).Value = UserForm1.TextBox1.Value
End Sub

Dim Ws As Worksheet
Set Ws = ThisWorkbook.Worksheets("Custom Buttons")
Dim LastRow As Long
LastRow = Ws.Cells(Ws.Rows.Count, "B").End(xlUp).Row + 1
Ws.Range("B" & LastRow).Value = UserForm1.TextBox1.Value

• Run the UserForm and enter text in the Text Box.
• Click the Name button to store the content in the Name column in your Excel sheet.
• Add content  to the Product and Revenue columns by clicking the Product and Revenue buttons.

[/wpsm_box]

How to Create a VBA Input Box with a Dropdown in Excel

Create an Input box with a Dropdown:

• Insert a UserForm and add a Combo Box and two Command Buttons.
• Rename the Command Buttons.

• Double-click the UserForm to enter the first part of the code.
• Double-click OK and Cancel to enter the rest of the code.
• The complete code is:

Private Sub UserForm_Initialize()
    Me.ComboBox1.List = Array("Keyboard", _
    "Printer", "Scanner", "Headset")
End Sub
Private Sub CommandButton1_Click()
    Dim Ws As Worksheet
    Set Ws = ThisWorkbook.Worksheets("Dropdown")
    Dim LastRow As Long
    LastRow = Ws.Cells(Ws.Rows.Count, "C").End(xlUp).Row + 1
    Ws.Range("C" & LastRow).Value = Me.ComboBox1.Value
    Unload Me
End Sub
Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Me.ComboBox1.List = Array("Keyboard", _
    "Printer", "Scanner", "Headset")
End Sub

Private Sub CommandButton1_Click()
    Dim Ws As Worksheet
    Set Ws = ThisWorkbook.Worksheets("Dropdown")
    Dim LastRow As Long
    LastRow = Ws.Cells(Ws.Rows.Count, "C").End(xlUp).Row + 1
    Ws.Range("C" & LastRow).Value = Me.ComboBox1.Value
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

• Run the UserForm and select an item in the dropdown.

Click OK to store the item in the selected cell.

Read More: Excel VBA Input Box with Drop Down List

Things to Remember

• Use Application.InputBox Method while using Input Box method with excel object to avoid errors.
• It is important to check if the entered value is valid or not to allow only one type of data in the Input Box.

Frequently Asked Questions

• How to Input a Box in VBA?

In the VBA module, enter InputBox and press Space to get the Input Box syntax. Specify the arguments.

• What is the difference between MsgBox () and InputBox ()?

A MsgBox displays messages in a dialog box whereas an InputBox is used to receive responses.

Download Practice Workbook

Download the practice workbook.

Related Articles

• How to Create Yes-No InputBox with Excel VBA
• Excel VBA: InputBox with Password Mask
• Excel VBA: Input Box with Options