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

Checks for the Cancel button clicks and takes no action.

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

Combine InputBox and Do Loop to read user input

 
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

Get the Password

 
     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

Get the return value from InputBox

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

How to handle error caused by value from InputBox

 
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

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.

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

Provide help file for InputBox

 
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

Read data from dialog box and fill it into the cell

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

Read password from InputBox

 
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

Reference InputBox from Application

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

Return a number from InputBox

 
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

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

 
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

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

 
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

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

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

Use If and IsNumeric function to check user input

 
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

Use InputBox to read value for an array

 
"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

Use parameter name in InputBox function

 
  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

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

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

Validate the user"s entry.

 
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

Validation with the InputBox() Function

 
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