Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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:

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:

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:

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:

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:

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:

Here we've used named arguments.

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

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

To practise using an Excel input box:

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

Sub WhatsTheDifference()

Application.InputBox "Enter your name"

End Sub

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

This is the type of input box you should see.

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

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

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

Run the subroutine and check that you see a sensible message:

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

Save and close the file.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Displaying an Excel Input Box

Capturing the Result

Customising the Appearance

Head office

London

Manchester