Asking Users for Data using a VBA Input Box

The VBA Input Box in an Excel MacroIf you are getting started working with Macros in Microsoft Office, there will be plenty of times when you want to ask a user for information. An Excel purchase log might ask how many widgets were ordered, or a Word time card might ask for a user’s name. Whatever information you want to request, the most common method of getting it is via an Input Box.

I’m going to create a macro in an Excel document for this example, but remember that this is VBA code; it will work just as well in a Word macro with only slight modifications. If you haven’t already, be certain to checkout the recent post Creating your first macro using visual basic for important information about getting started writing macros.

The secret to this technique is remembering the 1-2-3 process of VBA variables:

  1. You have to declare the variable using a special syntax
  2. You have to fill the variable by asking for user input, or performing some type of calculation.
  3. You have to output the results of the variable in your macro or your document

The code to ask a user to enter their name, and then output the result in cell A1 of the active Excel worksheet is as follows:

Sub inputBoxExample()
   'Step 1: Declare the variable
   Dim userName As String
   'Step 2: Fill the variable
   userName = InputBox("Please input your name.")
   'Step 3: Output the results into cell A1
   Range("A1").Value = userName
End Sub

Some key points are that

  • there are no spaces in the variable name (userName) or the function name (InputBox)
  • there is no space between the function name and the following open parenthesis.

 

Facebooktwitterlinkedinmail