VBA/Excel/Access/Word/Language Basics/InputBox

Checks for the Cancel button clicks and takes no action.

   <source lang="vb">

Sub GetValue2()

   UserEntry = InputBox("Enter the value")
   If UserEntry <> "" Then range("A1").value = UserEntry

End Sub

</source>
   
  

Combine InputBox and Do Loop to read user input

   <source lang="vb">

Sub GetValue3()

   MinVal = 1
   MaxVal = 12
   Msg = "Enter a value between 1 and 12"
   ValidEntry = False
   Do
       UserEntry = InputBox(Msg)
       If IsNumeric(UserEntry) Then
           If UserEntry >= 1 And UserEntry <= 12 Then
               ValidEntry = True
           Else
               Msg = "Your previous entry was INVALID. Enter a value between 1 and 12"
           End If
       End If
       If UserEntry = "" Then Exit Sub
   Loop Until ValidEntry
   ActiveSheet.Range("A1").Value = Val(UserEntry)

End Sub

</source>
   
  

Get the Password

   <source lang="vb">

    Sub GetPassword()
        Dim sPassWord As String, i As Integer
        i = 0
        Do
        i = i + 1
        If i > 3 Then
          MsgBox "Sorry, Only three tries"
          Exit Sub
        End If
        sPassWord = InputBox("Enter Password")
        Loop Until sPassWord = "XXX"
        MsgBox "Welcome"
    End Sub
</source>
   
  

Get the return value from InputBox

   <source lang="vb">

Sub InputBoxDemo()

   Dim myValue
   myValue = InputBox("Please enter number of hours worked", "Hours Worked")
  
   MsgBox myValue

End Sub

</source>
   
  

How to handle error caused by value from InputBox

   <source lang="vb">

Public Sub ErrorHandling()

   On Error GoTo ErrorHandling_Err
   Dim dblResult      As Double    " holds the result of the division
   dblResult = 10 / InputBox("Enter a number:")
   MsgBox "The result is " & dblResult

ErrorHandling_Exit:

   Exit Sub

ErrorHandling_Err:

   Select Case Err.Number
   Case 13                 " type mismatch - empty entry
       Resume
   Case 11                 " division by 0
       dblResult = 0
       Resume Next
   Case Else
       MsgBox "Oops: " & Err.Description & " - " & Err.Number
       Resume ErrorHandling_Exit
   End Select

End Sub

</source>
   
  

It is a good idea to include the title and default arguments to provide the user with a little help in knowing what to enter.

   <source lang="vb">

Sub GetRes()

   Dim retValue As String
   retValue = InputBox("Enter your name.", "Name", "First, Last")

End Sub

</source>
   
  

Provide help file for InputBox

   <source lang="vb">

Sub inputDemo()

   Dim strWhichOffice As String
   strWhichOffice = InputBox("Enter the name of the office that you visited:", _
       "Expense Assistant", "defaultValue", , , _
       "c:\Help.chm", 0)

End Sub

</source>
   
  

Read data from dialog box and fill it into the cell

   <source lang="vb">

Sub GetData()

  Sales = InputBox(Prompt:="Enter Target Sales")
  If Sales = "" Then Exit Sub
  Range("B2").Value = Sales

End Sub

</source>
   
  

Read password from InputBox

   <source lang="vb">

Sub GetPassword()

 Dim PassWord As String, i As Integer
 i = 0
 Do
   i = i + 1
   If i > 3 Then
     MsgBox "Sorry, Only three tries"
     Exit Sub
   End If
   PassWord = InputBox("Enter Password")
   Loop Until PassWord = "XXX"
 MsgBox "Welcome"

End Sub

</source>
   
  

Reference InputBox from Application

   <source lang="vb">

Public Sub InputBoxTest()

 Dim Answer As Range
 Set Answer = Application.InputBox(prompt:="Enter range", Type:=8)

End Sub

</source>
   
  

Return a number from InputBox

   <source lang="vb">

Sub AddTwoNums()

   Dim myPrompt As String
   Dim value1 As String
   Dim mySum As Single
   Const myTitle = "Enter data"
   myPrompt = "Enter a number:"
   value1 = InputBox(myPrompt, myTitle, 0)
   mySum = value1 + 2
   MsgBox mySum & "(" & value1 & "+2)"

End Sub

</source>
   
  

The InputBox function displays a dialog box containing a simple text box

   <source lang="vb">

Sub InputBoxExample()

  Dim strName As String
  strName = InputBox("What is Your Name?", "This is the Title", "This is the Default")
  msgBox "You Entered " & strName

End Sub

</source>
   
  

To make sure that the user has chosen the OK button, check that the input box hasn"t returned a zero-length string

   <source lang="vb">

Sub res()

   Dim strWhichOffice
   
   strWhichOffice = InputBox("Enter the name of the office:", _
       "Expense Assistant 2000", "default", , , _
       "c:\Help.chm", 0)
   If strWhichOffice = "" Then End

End Sub

</source>
   
  

To retrieve input from an input box, declare the numeric variable or String variable that will contain it

   <source lang="vb">

Sub help()

   Dim strWhichOffice
   strWhichOffice = _
       InputBox("Enter the name of the office:", _
       "Expense Assistant 2000", "default", , , _
       "c:\Help.chm", 0)

End Sub

</source>
   
  

Use If and IsNumeric function to check user input

   <source lang="vb">

Sub macro_if()

 Dim number
 number = InputBox("Type in a number, please!")
 If Not IsNumeric(number) Then
   MsgBox "This is no number!"
 ElseIf number > 10 Then
   MsgBox "The number is larger than 10."
 Else
   MsgBox "The number is smaller then or equal 10."
 End If

End Sub

</source>
   
  

Use InputBox to read value for an array

   <source lang="vb">

"Option Compare Database Option Explicit Public Sub ArrayExample()

   Dim i               As Integer
   Dim intNum(1 To 10)   As Integer
   For i = 1 To 10
       intNum(i) = InputBox("Please enter guess " & i, "Guess!")
   Next i
   For i = 1 To 10
       Debug.Print "Guess number " & i & " = " & intNum(i)
   Next i

End Sub

</source>
   
  

Use parameter name in InputBox function

   <source lang="vb">

 Sub GetRange()
     Dim Rng As Range
     On Error Resume Next
     Set Rng = Application.InputBox(prompt:="Specify a range:", Type:=8)
     If Rng Is Nothing Then Exit Sub
     MsgBox "You selected range " & Rng.Address
 End Sub
</source>
   
  

Using the InputBox Function: InputBox(prompt [, title] [, default] [, xpos] [, ypos] [, helpfile, context])

   <source lang="vb">

Sub Informant()

   InputBox prompt:="Enter your place of birth:" & Chr(13) & " (e.g., Boston, Great Falls, etc.) "

End Sub

</source>
   
  

Validate the user"s entry.

   <source lang="vb">

Sub GetValue3()

   MinVal = 1
   MaxVal = 12
   msg = "Enter a value between 1 and 12"
   ValidEntry = False
   Do
       UserEntry = InputBox(msg)
       If IsNumeric(UserEntry) Then
           If UserEntry >= 1 And UserEntry <= 12 Then
               ValidEntry = True
           Else
               msg = "Your previous entry was INVALID."
               msg = msg & "Enter a value between 1 and 12"
           End If
       End If
       If UserEntry = "" Then Exit Sub
   Loop Until ValidEntry
   ActiveSheet.range("A1").value = UserEntry

End Sub

</source>
   
  

Validation with the InputBox() Function

   <source lang="vb">

Sub Use()

   Dim userName As String
   Dim userBirthday As Date
   Dim nameOk As Boolean
   nameOk = True
   Do
       userName = InputBox("What is your first and last name?", "Name")
       If (userName <> "") Then nameOk = ValidateName(userName)
   Loop While (nameOk = False) Or (userName <> "")

End Sub Function ValidateName(userName As String) As Boolean

   Dim strLength As Integer
   Dim I As Integer
   Dim numSpaces As Integer
   Dim tempString As String
   Dim msb As Integer
   userName = Trim(userName)
   strLength = Len(userName)
   For I = 1 To strLength
       If Left(userName, 1) = " " Then
           numSpaces = numSpaces + 1
       End If
       userName = Right(userName, Len(userName) - 1)
   Next I
   If (numSpaces <> 1) Then
       ValidateName = False
       msb = MsgBox("Please enter just two names separated by one space", vbCritical, "Error")
   Else
       ValidateName = True
 End If

End Function

</source>