VBA InputBox window in Excel

Last Updated on January 11, 2023 by token

InputBox window – entering data into VBA

It’s great, that you have already reached this part of VBA tutorial. After the VBA MsgBox window you will learn how to work with window for entering data into our VB code. Like the MsgBox window, this window has many additional parameters. Let’s try to create a simple program for entering data into cell A1 into your data sheet.

'OfficeInside.Org
Sub InputExample()
 Range ("A1") = InputBox ("Complete cell A1")
End Sub

As you see, you can refer a single cell using the Range command with cell address. In the brackets of the InputBox command, enter the message that will be displayed before user will enter the data. The A1 cell can also be addressed using the Cells command , as follows:

'OfficeInside.Org
Sub InputExample1()
 Cells(1, 1) = InputBox ("Refill cell A1")
End Sub

InputBox window – additional arguments

Below is the full list of the InputBox VBA function containing all arguments. The only non-optional argument that should be filled is the Prompt argument. This is the message displayed before entering the value into the VBA code.

'OfficeInside.Org
Function InputBox(Prompt As String, [Title], [Default], [Left], [Top], [HelpFile], [HelpContextID])

Prompt – is a required argument. This is text displayed in the InputBox window.

Title – optional argument. The title of window, as in the case of the MsgBox window.

Default – Default value, displayed before user will enter any data.

Left – Defines the position of the window in pixels from the left side of your screen

Top – Defines the position of the window in pixels from the top of your screen

HelpFile – the argument is optional. The argument specifies the reference to the help file. The file number of the help file is specified in the HelpContextID argument.

HelpContextId – argument is optional. In the argument, you refer to the help file using the numerical value specified in the previous HelpFile.

Application examples – How to use the InputBox function window in Excel VBA?

Let’s imagine that you want to write a simple program that after entering the name in the VBA InputBox window will display a welcome message using the MsgBox window.

'OfficeInside.Org
Sub InputBoxExample3()

Dim StrName As String 'we declare a variable name
 StrName = InputBox ("Enter your name") 'we enter the value for your variable from the InputBox window
 MsgBox ("Welcome " & StrName & vbCrLf & "Have a good day!") 'MsgBox message

End Sub

As you can see, combining phrases in VBA is done using the “&” character. Alternatively, you can also use the “+” sign here. Between the name and the greeting, you put the Enter character using the ” vbCrLf ” element .

Let’s write a short program in which you will use most of the window arguments you have learned before. Below are the requirements:

• Let’s create the Input window for entering the year of birth
• The default value should be ie. 1990
• After entering the year of birth, the program should calculate the user’s age using the Date() and Year() functions
• The window should be located in the upper left corner of the screen
• At the end, the MsgBox window should be displayed with the user’s age and thanks for using program.

Example:

'OfficeInside.Org
Sub InputBoxExample4()

Dim strYear As String
 strYear = InputBox("Please enter your year of birth", "My window", 1990, 0, 0)
 MsgBox "You are: " & Year(Date) - strYear & " years old. " & vbCrLf & "Thank you for using my program!!!"

End Sub

I attach the file with examples below:
vba_inputbox_examples.xlsm

Excercises (You can enter the solution in a comment)

1. Create a program using MsgBox and VBA InputBox, filling 4 cells of our sheet from A1 to A4 with data: Name, Surname, Growth and Age. When finished, a message indicating the completion of the data entry should be displayed.

2. Improve your last program. When the message about entering data appears, display all entered data using one MsgBox window. Try to place the window in the bottom right corner of the screen. Set also default values.

More examples of Excel VBA basics can be found in the Excel VBA Tutorial section and  Excel VBA Examples section. If you have any questions about this chapter, you can post them on the forum without logging in.