This article was previously published under Q142141
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
In Microsoft Excel, you can create a Microsoft Visual Basic forApplications 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.
The InputBox method contains a type argument that you can use tospecify the type of data to be entered. You can recognize that theInputBox method is being used if "InputBox" is preceded by theApplication object. Also, if you click Cancel in the input box, the InputBox method returns False.
You must use conditionals such as If statements or Casestatements to verify the type of data that is entered. The InputBoxfunction can give you more control over the input and allows you to usecustom 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, "".
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements. The following examples display an input box that asks you for a number.When you enter a number and press ENTER or click OK, the number is written to cell A1 in the first worksheet of the active workbook. If you enter text, an error message appears and the box continues to be displayed. If you decide not to enter anything, you can click Cancel and the box is dismissed.
In the following example, the "1" just before the closing parenthesisindicates that only numbers are accepted by the input box. If you enteranything 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() Dim Response As Integer ' 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() Dim Show_Box As Boolean Dim Response As Variant ' 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
After you run the macro, the number you entered into the input box is in cell A1 on Sheet1.
For more information about the InputBox method, from the Visual Basic Editor, click the Office Assistant, type inputbox, click Search, and then click to view "InputBox Method."
For more information about the InputBox function, from the Visual Basic Editor, click theOffice Assistant, type inputbox, click Search, and then click to view "InputBox Function."
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
For more information about Input boxes in Microsoft Excel version 5.0,choose Programming with Visual Basic in Help; then click the Search button and type:
For additional information about getting help with Visual Basic forApplications, please click the article number below to view the article in the Microsoft Knowledge Base:
163435VBA: Programming Resources for Visual Basic for Applications
"Visual Basic User's Guide," version 5.0, Chapter 11, "Controls and Dialog Boxes"