InputBox

This is a VBA built in dialog box that can be used to prompt the user for information.
This type of message box displays a message and waits for the user to respond by pressing a button.
This function always returns a String so you often have to convert the value to its correct data type.
Even if a numerical value is entered it will be returned as a string.

If the box is empty and the user presses OK (or Enter), then a zero length string is returned.
If the box contains a default value and the user presses OK (or Enter), then the default value is returned.
If the user presses Cancel (or Esc), then a zero length string is returned.
It is always a good idea to test the result before proceeding, so this situation can be handled.
Scroll down to the Pressing Cancel heading below, for a code snippet.


Excel - Application.InputBox

Excel also has its own InputBox function which can cause a lot of confusion.
This inputbox is very similar to the VBA one, the only difference is that with the Excel one you can specify the datatype that is returned.
For more information, please refer to the Excel > Macros > Application.InputBox page.


Displaying a Prompt

This function will prompt the user to enter the name of the file.
The value returned from this function is always a string.

Dim sReturn As String 
sReturn = InputBox("Please enter the name of the file : ")

sReturn = InputBox(prompt [, title] [, default] [,xpos] [,ypos] [, helpfile, context])

promptRequired. String expression containing the message to be displayed. The maximum length of a single line prompt is approximately 1024 characters, depending on the width of the characters used. You can create a multi line message by using the carriage return character vbCrLf (Chr(13)), a linefeed character (Chr(10)), or carriage return-linefeed character combination (Chr(13) & Chr(10)) between each line.
titleOptional. String expression displayed in the title bar of the dialog box. If you omit title, the application name is placed in the title bar.
defaultOptional. String expression displayed in the text box as the default response if no other input is provided. If you omit default, the text box is displayed empty.
xposOptional. Numeric expression that specifies, in twips, the horizontal distance of the left edge of the dialog box from the left edge of the screen. If xpos is omitted, the dialog box is horizontally centered.
yposOptional. Numeric expression that specifies, in twips, the vertical distance of the upper edge of the dialog box from the top of the screen. If ypos is omitted, the dialog box is vertically positioned approximately one-third of the way down the screen.
helpfileOptional. String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided.
contextOptional. Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.

Pressing Cancel

If the user presses Cancel (or Esc), then a zero length string is returned.
You can check for whether the Cancel (or Esc) key was pressed because the StrPtr of the result will be 0.
Thank you to Marshall Brooks for providing the following code snippet:

Dim sReturn As String 
sReturn = InputBox("Please enter the name of the file : ")

If StrPtr(sReturn) = 0 Then
    Debug.Print "Cancel or ESC pressed"
               
ElseIf sReturn = "" Then
    Debug.Print "OK pressed, No value entered, No default value"
               
Else
    Debug.Print "OK pressed, Value entered or a Default value"
End If

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext