Module 5 - Messages and User Inputs
Lesson 5.4 - The Excel Input Box
Topic 5.4.1 - Using the Excel Input Box

Up to this point we've been using the generic VBA input box to ask users for input.  Microsoft Excel has its own version of an input box with several additional features which make it more useful than the standard version:

Excel input box

The Excel input box doesn't look much different to the generic VBA version, but it has a few hidden tricks which make it much more useful!

 

Files Needed

You don't need any files for this section.

Completed Code

Click here to download the sample file used on this page.

Create a new workbook and insert a new module in the VBE.

Displaying an Excel Input Box

The Excel-specific InputBox function is a member of the Application object.  To display this type of input box, you must start by referring to the Application object.  Create a new subroutine called ExcelInputBox and add code to it as shown below:

display input box

To access the Excel input box, write Application.InputBox in your code.

 

The Excel InputBox function has eight parameters in total, but only the Prompt parameter is required:

Basic code

This code is sufficient to make an Excel input box appear.

 

Running the code shown above produces an input box as shown in the diagram below:

Basic input box

So far, so simple.

 

If you're going to be using a mix of VBA and Excel input boxes in your code, you might find it useful to always be explicit about which type you're using:

'show a generic VBA input box

VBA.InputBox "Enter your name"

 

'show an Excel-specific input box

Application.InputBox "Enter your name"

Capturing the Result

You can capture the result of an Excel input box in the same way as for the generic VBA version.  Add code to declare a String variable and return the result of the input box to the variable:

Capture response

By default the input box returns a string, so you can capture it in a String variable. Don't forget to enclose the argument list in parentheses (round brackets) when you want to capture the return value of the input box.

 

Customising the Appearance

You can customise the appearance of an Excel input box by changing its title and providing a default value.  Add code to display a title and default value on the input box:

Named arguments

Here we've used named arguments.

 

Running the code shown above produces an input box similar to the one shown below:

Customised input box

Sadly, this is all you can do to alter the appearance of an input box.

 

To practise using an Excel input box:

  1. Using any workbook, insert a new module and create a subroutine called WhatsTheDifference.
  2. Add code to display an Excel input box:

Sub WhatsTheDifference()

 

Application.InputBox "Enter your name"

 

End Sub

  1. Run the subroutine and check that you see an Excel input box:
Excel input box

This is the type of input box you should see.

 
  1. Declare a string variable to capture the value returned by the input box:

Sub WhatsTheDifference()

 

Dim YourName As String

 

Application.InputBox "Enter your name"

 

End Sub

  1. Store the result of the input box in the variable you have declared:

Sub WhatsTheDifference()

 

Dim YourName As String

 

YourName = Application.InputBox("Enter your name")

 

End Sub

  1. Use the value you have captured to display a message to the user:

Sub WhatsTheDifference()

 

Dim YourName As String

 

YourName = Application.InputBox("Enter your name")

 

MsgBox "Hello " & YourName

 

End Sub

  1. Run the subroutine and check that you see a sensible message:
Message box

Feel free to embellish the message box with a title and icon.

 
  1. Save and close the file.
This page has 0 threads Add post