Collect User Data/Input via an InputBox

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

Excel VBA: Gather User Data/Input via an InputBox

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Back to: Excel VBA . Got any Excel/VBA Questions? Free Excel Help

There are many times in Excel VBA that we are required to gather information from a user. Probably the most frequent method of doing is via a message box, that is;

Sub UserInput()
Dim iReply As Integer

    iReply = MsgBox(Prompt:="Do you wish to run the 'update' Macro", _
            Buttons:=vbYesNoCancel, Title:="UPDATE MACRO")
            
    If iReply = vbYes Then
        Run "UpdateMacro"
    ElseIf iReply = vbNo Then
       'Do Other Stuff
    Else 'They cancelled (VbCancel)
        Exit Sub
    End If
    
End Sub

As you can though, the message box approach only allows for pre-determined responses. To actually allow the user to enter some text, number or even a formula we can use the InputBox Function. The syntax for the InputBox Function is;

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

It is rare that you will need to use [, xpos] [, ypos] or [, helpfile, context]. See Excel help for details on these. It should also be noted that, the InputBox Function returns a String only when used in this way. (more on another way soon).

Ok, lets assume we need to gather the name of the user and do some stuff depending on that name. The macro below will achieve this.

Sub GetUserName()
Dim strName As String

    strName = InputBox(Prompt:="You name please.", _
          Title:="ENTER YOUR NAME", Default:="Your Name here")
          
        If strName = "Your Name here" Or _
           strName = vbNullString Then
           Exit Sub
        Else
          Select Case strName
            Case "Bob"
                'Do Bobs stuff
            Case "Bill"
                'Do Bills stuff
            Case "Mary"
                'Do Marys stuff
            Case Else
                'Do other stuff
          End Select
        End If
  
End Sub

Note the use of the Select Case Statement to determine the name the user supplies.

Application.InputBox

When we precede the InputBox Function with "Application" we get an InputBox Method that will allow us to specify the type of info that we can collect. Its Syntax is;

InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)

As you can see, the Prompt, Title and Default are the same as in the InputBox Function. However, it is the last argument "Type" that allows us to specify the type of data we are going to collect. These are as shown below;

Type:=0 A formula
Type:=1 A number
Type:=2 Text (a string)
Type:=4 A logical value (True or False)
Type:=8 A cell reference, as a Range object
Type:=16 An error value, such as #N/A
Type:=64 An array of values

We have already covered a String being returned so lets look, what I believe, to be the most useful. That is, Type 8 & 1. The code below shows how we can allow the user to specify a Range Object.

Sub RangeDataType()
Dim rRange As Range

    On Error Resume Next
        Application.DisplayAlerts = False
            Set rRange = Application.InputBox(Prompt:= _
                "Please select a range with your Mouse to be bolded.", _
                    Title:="SPECIFY RANGE", Type:=8)
    On Error GoTo 0
        Application.DisplayAlerts = True

        If rRange Is Nothing Then
            Exit Sub
        Else
            rRange.Font.Bold = True
        End If
        
End Sub

Note the use of both, On Error Resume Next and Application.DisplayAlerts = False. These stop Excel from trying to handle any bad input from the user, or if they Cancel. Take the lines out, run the code and click Cancel, or specify a non valid range and Excel will bug out in the case of Cancel.

Let's now look at how we can collect a numeric value from a user.

Sub NumericDataType()
Dim lNum As Long

    On Error Resume Next
        Application.DisplayAlerts = False
            lNum = Application.InputBox _
             (Prompt:="Please enter you age.", _
                    Title:="HOW OLD ARE YOU", Type:=1)
    On Error GoTo 0
    Application.DisplayAlerts = True

        If lNum = 0 Then
            Exit Sub
        Else
            MsgBox "You are " & lNum & " years old."
        End If
        
End Sub

Again, we take over the possibility of the user electing to Cancel, or entering a non-numeric value. If they enter anything that is not numeric and click OK, they are taken back to the InputBox Method with their entry highlighted.

Unlike the InputBox Function, we can combine different Types for the InputBox Method and take action based on their data type. See example.

Sub Numeric_RangeDataType()
Dim vData

    On Error Resume Next
        Application.DisplayAlerts = False
        
            vData = Application.InputBox _
             (Prompt:="Please select a single cell housing the number, " _
             & "or enter the number directly.", _
             Title:="HOW OLD ARE YOU", Type:=1 + 8)
             
    On Error GoTo 0
        Application.DisplayAlerts = True

    If IsNumeric(vData) And vData <> 0 Then
        MsgBox "You are " & vData & " years old."
    Else
       Exit Sub
    End If
End Sub

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft