How to Use VBA Input Box with Buttons in Excel (2 Examples)

Input Box provides a simple way to get responses from the users. It accepts all types of data such as strings, numbers, formulas, ranges, etc. Input Box can display different buttons which are OK, Cancel, Yes, No, Retry, etc. depending on the Type argument. In this article, we will demonstrate two practical examples to use VBA Input Box with Buttons in Excel.

Introduction to Input Box in Excel

Input Box is a dialogue box that takes input from the users. An Input Box has two buttons which are OK and Cancel. If the user clicks OK, it returns the entered value in the Input Box. If the Cancel button is clicked, it returns False.

Syntax of Input Box in Excel

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

Arguments	Required/Optional	Function
Prompt	Required	Displays message in the dialogue box
Title	Optional	Displays a string expression in the title bar
Default	Optional	Shows a default response in the Input Box
XPos	Optional	Specifies the horizontal position of the Input Box
YPos	Optional	Specifies the vertical position of the Input Box
HelpFile	Optional	Identifies the help file related to the Input Box
Context	Optional	Provides context ID for the help file

How to Launch VBA Editor in Excel

To access the Microsoft Visual Basic window, go to the Developer tab and then click on Visual Basic. You can also open it by pressing Alt+F11 on your keyboard.

Go to the Insert tab and click on Module to launch code Module.

Opening User Form

Once the Microsoft Visual Basic window is open, click on the Insert tab and select Userform.
As a result, an Userform will open with a Toolbox.

Use VBA Input Box with Buttons in Excel: 2 Practical Examples

In this article, we will show you 2 examples of how to use VBA Input Box with Buttons in Excel.

1. Insert Data Using Input Box in Excel

1.1. Insert String to a Cell

In this example, we will insert String type data using an Input Box. We will write a code that only accepts a string. The steps to do so are discussed below.

First of all, open Microsoft Visual Basic code Module.
Then write the 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

Code Breakdown:

Name the sub procedure and declare the variables.

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

It counts the number of non-blank cells in B5 to B12 range
Pops up an Input Box to enter a name and stores this name in cell B5.
Next entry is stored in cell B6.

Afterwards, run the code using the F5 key or Run button.
An Input Box will appear. Insert a name and it will be stored in your desired cell.

1.2. Accept Only Numbers in Input Box

In this example, we will create an Input Box that only allows Numbers as input. We will take these inputs and store them in our excel sheet.

First, in the code Module, write the code given below.

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

Code Breakdown

Name the sub procedure and declare variables.

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

An Input Box will appear and ask the users to enter revenue earned. This Input Box accepts numbers as input only.
The entered number is stored in cell E5.

An Input Box will pop up once you run the code.
Enter a number to store it in your desired cell.

1.3. Insert Date Using Input Box

Now we will use an Input Box to insert a date in our Excel workbook. We will follow these steps for this purpose.

First, go to Microsoft Visual Basic code Module and insert 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

Code Breakdown

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

It finds the next available row in column E and stores a date.

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

If the entered date is valid, it is stored in dd/mm/yyyy

Finally, run the code and enter a date in the Input Box.

1.4. Apply Formula in Input Box

We can also apply formulas simply using the Input Box. We will sum the total revenue earned using the formula in this example. For this purpose, we will need a code that makes the Input Box take formulas as input.

First, write the code given below in the code 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

Code Breakdown

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

If the user enters a valid formula, it inserts it into an active cell.

Insert a formula in the Input box that will appear after running the code.
As a result, you will get your desired result.

1.5. Insert Cell Reference in Input Box

In this example, we will insert a cell reference in an Input Box and change the color of the entered cells. Follow these steps to learn how to do it.

First of all, launch the Visual Basic
Next, write 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

Code breakdown

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

It takes the cell range in the Input box and changes the color of the entered range.

After that, run the code and insert a range in the Input Box.
As a result, the range will turn yellow.

2. Calculate Area of Triangle Using Input Box in Excel

In this example, we will take the value of base and height in the Input Box to calculate the area of a triangle.

First, insert the following code in the code 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

Code Breakdown

  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

It takes the value of base and height as inputs and calculates the area of a triangle.
Then stores the value of area in column D starting from cell D5.

Then, run the code and insert the values of base and height to calculate the area of the triangle.

As a result, you will get the result in your desired cell.

How to Check If Cancel Button Is Pressed or Not in Input Box with Excel VBA

In this example, we will write a code to show “You pressed cancel” if the user presses the Cancel button in the Input Box and if the user leaves the Input Box blank, it will display “Nothing is entered”.

First, launch the Microsoft Visual Basic code Module.
Then insert 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

After that, run the code and enter nothing in the Input Box.
It will open a MsgBox that says “Nothing is entered”.

InputBox vs Application.InputBox Method

Excel provides the Input Box to obtain input from the user. The Input Box can be accessed using the InputBox Function or the InputBox Method. The Input Box method can be invoked by using Application.InputBox. The main difference between the two methods is that the Input Box Method allows validation of the entered data. The Input Box Method has an additional argument called “Type” which specifies the data type to be entered. If the user clicks the Cancel button, the Input Box Method returns False while the Input Box Function returns an empty text string.

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

We want to create an Input Box that can take multiple inputs. It can be done by creating a UserForm and making it act as an Input Box. The advantage of this method is that you can design the UserForm as per your need. We will explain the procedure to create a VBA Input Box with multiple inputs in the following section.

First, open the Microsoft Visual Basic window.
Then insert a UserForm.
After that, drag Labels, Text Boxes, and Command Buttons from the Toolbox and drop them in the UserForm.
Change the captions of the Labels and Command Buttons.
Resize and adjust their positions as per your need.

Now double-click on the OK button and Cancel button to write 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

Code Breakdown

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

Finds the last non-empty cells in a row and stores the values inserted in the text boxes.

Run the code. It will open a UserForm.
Insert data in all three Text boxes and click OK.
As a result, all the data will be stored in the desired cells.

How to Create VBA Input Box with Custom Buttons in Excel

In this example, we will create a VBA Input Box that has customized buttons. For this purpose, we will design a UserForm that will act as an Input Box. We will be able to change the caption of the buttons. Moreover, we will be able to resize and change the position of the buttons.

First of all, insert a UserForm and add Text Box and Command Buttons.
Then change the Command Button captions as per your need.

Now we need to write code for each Command Buttons. For this purpose, we will double-click each Command Button and write 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

Code Breakdown

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

It finds the last row with data and goes to the next empty row.
Then it inserts the content of the Text Box in that empty row.

Now run the UserForm and insert anything in the Text Box.
Then press the Name button to store the content in the Name column in your Excel sheet.
Similarly, you can add contents in the Product and Revenue column by pressing Product and Revenue buttons.

[/wpsm_box]

How to Create VBA Input Box with Dropdown in Excel

In this example, we will create an Input box that will have a Dropdown where users can select anything from a list of items. We will need to create a UserForm for this purpose.

First, insert a UserForm and add a Combo Box and two Command Buttons.
Rename the Command Buttons.

Next, double-click the UserForm to write the first part of the code.
Then double-click the OK and Cancel buttons to write the rest of the code.
The complete code is given below.

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

Code Breakdown

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

This part adds items to the ComboBox dropdown when the UserForm is loaded.

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

This portion runs when the OK button is clicked. It stores the content of the dropdown that the user selected in a cell.

Private Sub CommandButton2_Click()
    Unload Me
End Sub

This part unloads the UserForm when the Cancel button is clicked.

After that, run the UserForm and select an item from the dropdown.

Finally, press OK to store the item in your desired cell.

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, type InputBox and press the Space button to get Input Box syntax. Then you can specify the arguments as per your need.

What is the difference between InputBox and application InputBox in Excel VBA?

The main difference between the two methods is that the Input Box Method (Application.inputBox) allows validation of the entered data. The Input Box Method has an additional argument called “Type” which specifies the data type to be entered. If the user clicks the Cancel button, the Input Box Method returns False while the Input Box Function returns an empty text string.

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

MsgBox displays messages in a dialogue box whereas InputBox is used to receive responses from the users.

Download Practice Workbook

Download this practice workbook to exercise while reading this article.

Excel VBA: Input Box with Buttons.xlsm

Conclusion

Thanks for making it this far. I hope you find this article useful. In this article, we demonstrated examples to create InputBox that allows specific types of data and how we can store the entered data in the Excel sheet. We also created Input Boxes that can take multiple inputs, have custom buttons and dropdown. We used the UserForm for these purposes as it can easily be modified. If you have any queries related to this article, feel free to ask us in the comment section.

Get FREE Advanced Excel Exercises with Solutions!

How to Use VBA Input Box with Buttons in Excel (2 Examples)

Introduction to Input Box in Excel

Syntax of Input Box in Excel

How to Launch VBA Editor in Excel

Use VBA Input Box with Buttons in Excel: 2 Practical Examples

1. Insert Data Using Input Box in Excel

1.1. Insert String to a Cell

1.2. Accept Only Numbers in Input Box

1.3. Insert Date Using Input Box

1.4. Apply Formula in Input Box

1.5. Insert Cell Reference in Input Box

2. Calculate Area of Triangle Using Input Box in Excel

How to Check If Cancel Button Is Pressed or Not in Input Box with Excel VBA

InputBox vs Application.InputBox Method

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

How to Create VBA Input Box with Custom Buttons in Excel

How to Create VBA Input Box with Dropdown in Excel

Things to Remember

Frequently Asked Questions

Conclusion

Related Articles

Leave a reply Cancel reply