EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

 Excel Training VBA Lesson 9

 

InputBox/Input Box Function

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX

INPUT BOX

Another very handy function is the InputBox function. This will allow us to collect a reply from a user in the form of text.  Its syntax is:

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

The only required argument is the Prompt and this would be the message you display to the user. 

Try this example below:


Sub WhatsYourName()
Dim stReply As String
'<any code>

    stReply = InputBox(Prompt:="What is your name?", _
                Title:="OzGrid Example", Default:="Mine is David!")
       If stReply <> "" _
        And stReply <> "Mine is David!" Then
            MsgBox "Hello " & stReply
       End If

End Sub


In the above example we are asking the user to tell us their name. We have put a default message in the InputBox telling them our name. As with the message box function, we are using a variable to determine their reply. The difference is though, the InputBox returns a String and not an Integer! If the user decides to Cancel the InputBox it would return empty text (""), if they simply clicked Ok without changing our Default message it would return: "Mine is David!". Both of these possibilities are dealt with in our If And combination.

There is also another type of InputBox, which is a member of the Application Object. This InputBox will allow us to specify what type of value to pass back to our InputBox. The syntax for this InputBox is:

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

Again the only required argument is the Prompt. The important part to note with this type of InputBox is that you must precede it with an Application Object, which in Excel's case in the actual word Application

The return value type is specified by the Type argument and is shown in the table below taken from the Excel help.:

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

You can use the sum of the allowable values for Type. For example, for an input box that can accept both text and numbers, set Type to 1 + 2.

So to use the InputBox to return a range Object you could use:


Sub Demo()

Dim rMyRange As Range

On Error Resume Next
    Set rMyRange = Application.InputBox _
    (Prompt:="Select any range", Title:="OzGrid Example", Type:=8)
        rMyRange.Select
On Error GoTo 0
End Sub


Notice the use of On Error Resume Next this is used to prevent any possible run time errors that would occur if the user clicks Ok (without select a range) or Cancel. If they did, our Set Statement would not be valid. I have then used the On Error GoTo 0 to reset all run time errors. The use of these two Statements are discussed in a later lesson.

As you can see, with the use of Excels built in function such as MsgBox and InputBox, we are able to communicate with the user. More importantly, we can collect their replies and answers.

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX