How to use the input screen "InputBox" Excel Macro (VBA)

If you want to use values or arbitrary characters that do not exist in Excel, the "InputBox" is useful for easily creating an input screen during macro execution.

In this issue, we will briefly explain how to use this "InputBox.

Sub Input Screen()
    Dim Input Value As String
    Input Value = InputBox("Please tell me your name")
    MsgBox ("Hello" & InputValue & "Ms.")
End Sub

How to use InputBox

The syntax of InputBox is very easy to write if you want to use simple input commands, as described at the beginning of this section, but in fact there are many arguments as shown below.

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

Since we are here, let us look at them one by one.

Prmpt message (not optional)

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

Promptschool (e.g. of ikebana)cannot be omittedHowever, other arguments can be omitted. For example, the following is an example.

Sub Input Screen()
    InputBox ("What is your name?")
End Sub

When this command is executed, the following input screen will appear.

However, this alone does not cause anything to happen, whether you press "OK" or "Cancel".

So let's change it like this.

Sub Input Screen 1()
    Dim Input Value As String
    Input value = InputBox("Please tell me your name")
    MsgBox ("Hello" & input value & "Ms")
End Sub

In this way, the entered value can be used in the form of a message box.

Furthermore, the message box is forced to appear when the input value is blank or when cancel is pressed, and we would like to make the cancel message appear in these cases.

Sub Input Screen()
    Dim Input Value As String
    Input Value = InputBox(Prompt:="Tell me your name")
    If Input value = "" Then
        MsgBox ("Could not get the name." & vbCrLf & "Please enter the name again.")
        End
    Else
        MsgBox ("Hello" & input value & "Ms")
    End If
End Sub

Execute this code and press the "OK" or "Cancel" button with the name blank,

I was able to display the following information.

Title Title (may be omitted)

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

Titleis optional and does not need to be included. However, it can be entered to display the title and make it look good.

Sub Input Screen()
    Dim Input Value As String
    Input Value = InputBox("Please tell me your name"), "Question 1")
    If Input value = "" Then
        MsgBox ("Could not get the name." & vbCrLf & "Please enter the name again.")
        End
    Else
        MsgBox ("Hello" & Input Value & "Mr.")
    End If
End Sub

Default Default Default (can be omitted)

Defaultis optional and need not be entered. However, if you enter it, you can set an "initial value" in the input box, which is useful when you want to give specific instructions on what values you want the user to enter.

Sub Input Screen()
    Dim Input Value As String
    Input Value = InputBox("Tell me your name", "Question 1"), "Captain's name")
    If Input value = "" Then
        MsgBox ("Could not get the name." & vbCrLf & "Please enter the name again.")
        End
    Else
        MsgBox ("Hello" & Input Value & "Mr.")
    End If
End Sub

Here, since we brought the "initial value" immediately after the "title", this was recognized as the initial value, but if the title is omitted, it will not be recognized as the "initial value".

If you omit the arguments in between, you can specify the "initial value" by specifying the name of the argument as shown below.

Sub Input Screen()
    Dim Input Value As String
    Input Value = InputBox(Prompt:="Please tell me your name",. Default:="Captain's name")
    If Input value = "" Then
        MsgBox ("Could not get the name." & vbCrLf & "Please enter the name again.")
        End
    Else
        MsgBox ("Hello" & Input Value & "Mr.")
    End If
End Sub

XPos and YPos (optional)

Two items are introduced at the same time. This item specifies where the input dialog is to be displayed.

XPos" is the horizontal position and "YPos" is the vertical position.

Sub Input Screen()
    Dim Input Value As String
    Input Value = InputBox(Prompt:="Please tell me your name",. XPos:=1000, YPos:=1000)
    If Input value = "" Then
        MsgBox ("Could not get the name." & vbCrLf & "Please enter the name again.")
        End
    Else
        MsgBox ("Hello" & Input Value & "Mr.")
    End If
End Sub

You can actually try different numbers to see how much place is just right.

Note that if you put in a number so large that it jumps out of the screen, you will not be able to press the button, but rather it will stop with an error.

HelpFile and Context (optional)

These two are used as a set, so here are two at the same time.

HelpFile can be a "help file" created separately, and Context can be the number of lines in the help file.

Sub Input Screen()
    Dim Input Value As String
    Input Value = InputBox(Prompt:="Please tell me your name",. HelpFile:="help.hlp", Context:=10)
    If Input value = "" Then
        MsgBox ("Could not get the name." & vbCrLf & "Please enter the name again.")
        End
    Else
        MsgBox ("Hello" & Input Value & "Mr.")
    End If
End Sub

When I run this code

The Help button could be displayed.