XL: How to Use Input Boxes with Visual Basic for Applications

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel for the Power Macintosh, versions 5.0, 5.0a

SUMMARY

In Microsoft Excel, you can create a Microsoft Visual Basic for Applications procedure that uses an input box to gather data from a user. To create an input box, you can use the InputBox method or the InputBox function. The main difference between the InputBox method and the InputBox function is how you use them to check the entered data to see if it is correct.

   InputBox Method
   ---------------

   The InputBox method contains a type argument that you can use to
   specify the type of data to be entered. You can recognize that the
   InputBox method is being used if "InputBox" is preceded by the
   Application object. Also, if you click Cancel in the input box, the
   InputBox method returns False.

   InputBox Function
   -----------------

   You must use conditionals such as If statements or Case
   statements to verify the type of data that is entered. The InputBox
   function can give you more control over the input and allows you to use
   custom error messages. You can distinguish the InputBox function from
   the InputBox method by the Application object; the function is not
   preceded by the Application object. In addition, the function lacks an
   argument for checking the type of the entered data. If you click the
   Cancel button in an input box, the InputBox function returns an empty
   text string, "".

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

InputBox Method

In the following example, the "1" just before the closing parenthesis indicates that only numbers will be accepted by the input box. If you enter anything else, such as text or a formula, the InputBox method displays an error message. The macro uses the If statement to see if Cancel is clicked. If Cancel is not clicked, the macro writes the entered value to cell A1 on the first worksheet.

   Sub Using_InputBox_Method()

      ' Run the Input Box
      Response = Application.InputBox("Enter a number.", _
         "Number Entry", , 250, 75, "", , 1)

      ' Check to see if Cancel was pressed.
      If Response <> False Then

         ' If not, write the number to the first cell in the first sheet.
         Worksheets(1).Range("a1").Value = Response

      End If

   End Sub

The Input Box Function

In the example below, a series of If statements is used to check the entry. The InputBox is inside a While loop to allow it to be re-shown if an error occurs. If all the conditions are true, the entered number is written to cell A1 on the first worksheet and the loop is ended.

   Sub Using_InputBox_Function()

      ' Set the Show_Dialog variable to True.
      Show_Box = True

      ' Begin While loop.
      While Show_Box = True

         ' Show the input box.
         Response = InputBox("Enter a number.", _
            "Number Entry", , 250, 75)

         ' See if Cancel was pressed.
         If Response = "" Then

            ' If Cancel was pressed,
            ' break out of the loop.
            Show_Box = False
         Else
            ' Test Entry to find out if it is numeric.
            If IsNumeric(Response) = True Then
               ' Write the number to the first
               ' cell in the first sheet in the active
               ' workbook.
               Worksheets(1).Range("a1").Value = Response
               Show_Box = False
            Else
               ' If the entry was wrong, show an error message.
               MsgBox "Please Enter Numbers Only"
            End If
         End If
      ' End the While loop.
      Wend
   End Sub

REFERENCES

"Visual Basic User's Guide," version 5.0, Chapter 11, "Controls and Dialog Boxes"

For more information about Input Boxes in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type:

   tell me about an inputbox

   input

   ARTICLE-ID: Q163435
   TITLE     : VBA: Programming Resources for Visual Basic for
               Applications