An Input Box example

Here's an example showing how you can use the InputBox function:

TheName = InputBox("What is your name?", "Greetings")

When you execute this VBA statement, Excel displays the dialog box shown in Figure 15-4. Notice that this example uses only the first two arguments and does not supply a default value. When the user enters a value and clicks OK, the routine assigns the value to the variable TheName.

Figure 15-4:

The InputBox function displays this dialog box.


What is your name? I; OK il


The following example uses the third argument and provides a default value. The default value is the username stored by Excel (the Application object's UserName property).



Dim DefName As String

Dim TheName As String

DefName = Application.UserName

TheName = InputBox("What is your name?", _

"Greetings", DefName)


VBA's InputBox function always returns a string, so you might need to convert the result to a value. You can convert a string to a value by using the Val function.

The following example uses the Val function to convert the user's entry to a value:



Dim Prompt As String

Dim Caption As String

Dim DefValue as Integer

Dim NumSheets As Integer

Prompt = "How many sheets do you

want to add?"

Caption = "Tell me..."

DefValue = 1

NumSheets = Val(InputBox(Prompt,

Caption, DefValue))

If NumSheets > 0 Then Sheets.Add




Figure 15-5 shows the dialog box that this routine produces.

Figure 15-5 shows the dialog box that this routine produces.

Figure 15-5:

Another example of using the InputBox function.

Tell me...

How many sheets do you want to add?


Cancel |

The information presented in this section applies to VBA's InputBox function. In addition, you have access to the InputBox method, which is a method of the Application object. One advantage of using the InputBox method is that your code can prompt for a range selection. Here's a quick example that prompts the user to select a range. (The Help system has complete details.)



Dim Rng As Range

On Error Resume Next

Set Rng = Application.InputBox

(prompt:="Specify a range:",


If Rng Is Nothing Then Exit Sut


MsgBox "You selected range " &




Was this article helpful?

0 0

Post a comment