Learn Excel techniques, follow the WeChat public account:
excelperfect
Refer to the previous article: Complete Introduction to VBA String Knowledge>>>
Message Box
A message box is a special dialog box used to display a message to the user. The user cannot type anything in the message box. Typically, two types of message boxes are created: one that simply displays information and another that expects the user to make a decision.
Use the MsgBox function to create a message box. Its syntax is:
Function MsgBox(Prompt[, Buttons][, Title][, Helpfile, Context]) As String
The MsgBox function accepts five parameters, with only the first parameter being required.
Message in the Message Box
The Prompt parameter is the string that the user will see displayed in the message box. As a string, it can be displayed with double quotes, such as “Your credentials have been checked”. Here’s an example:
Sub Exercise17()
MsgBox (“Your credentials have been checked.”)
End Sub
The result is shown in the following figure 6.
Figure 6
You can also create messages from other string fragments. The Prompt parameter can consist of up to 1024 characters. To display the prompt in multiple lines, you can use the constant vbCrLf or combine Chr(10)& Chr(13). Here’s an example:
Sub Exercise18()
MsgBox (“Your login credentials have been checked.” & _
vbCrLf & “To complete the application,” & _
“Please fill out the survey below“)
End Sub
The result is shown in the following figure 7.
Figure 7
If you call the MsgBox function using only the first parameter, the function is called a method (a method is a member function of a class; in this case, the class is the application being dealt with). If you want to use other parameters, you must treat MsgBox as a function. That is, you must assign it to a variable or object.
Buttons in the Message Box
The Buttons parameter specifies which buttons should be displayed on the message box. Different types of buttons are available, each using a constant integer, as shown below:
Figure 8
When calling the MsgBox function and specifying buttons, you can use one of the constant values mentioned above. The following example displays “Yes” and “No” buttons on the message box:
Sub Exercise19()
ActiveCell = MsgBox(“Your credentials have been checked” & _
“Your application has been authorized:” & _
“Congratulations!” & vbCrLf & _
“Before you leave, would you like to take our survey?”, vbYesNo)
End Sub
The result is shown in the following figure 9.
Figure 9
Icons in the Message Box
In addition to buttons, to enhance the functionality of the message box, you can also display an icon on the left side of the message box. To display an icon, you can use or add members of the MsgBoxStyle enumeration. The members that display icons include (the table shows icon constants, numeric values, and descriptions):
Figure 10
To use one of these icons, you must combine the button value with the desired icon value. To perform this combination, use the OR operator. Here’s an example:
Sub Exercise20()
Dim iAnswer As Integer
iAnswer = MsgBox(“Your credentials have been checked” & _
“Your application has been authorized:” & _
“Congratulations!” & vbCrLf & _
“Before you leave, would you like to take our survey?”, _
vbYesNo Or vbQuestion)
End Sub
The result is shown in the following figure 11.
Figure 11
When calling the MsgBox function, if you want to display one or more buttons and an icon, you can use the OR operator to use two members of the MsgBoxStyle enumeration, or you can add a constant value for buttons to another constant value for icons. For example, 3+48=51 will display the “Yes”, “No”, and “Cancel” buttons along with an exclamation icon.
Default Button of the Message Box
If you create a message box that contains multiple buttons, the leftmost button usually has a thick border, indicating that it is the default. If the user presses the Enter key after viewing that button, it will have the same effect as clicking the default button. If needed, you can specify another button as the default button. To do this, you can use or add another member of the MsgBoxStyle enumeration. The members used to specify the default button include:
Figure 12
Similarly, to specify a default value, use the OR operator to combine the default button constant with any other combination. Here’s an example:
Sub Exercise21()
ActiveCell = MsgBox(“Your credentials have been checked” & _
“Your application has been authorized:” & _
“Congratulations!” & vbCrLf & _
“Before you leave, would you like to take our survey?”, _
vbYesNo Or vbQuestion Or vbDefaultButton2)
End Sub
The result is shown in the following figure 13.
Figure 13
These additional buttons can be used to further control the actions the user can perform:
Figure 14
Title of the Message Box
The Title parameter is the title displayed on the message box title bar. It is a string whose words or sentences can be enclosed in parentheses or can be derived from the created string. The Title parameter is optional. If omitted, the message box will display the name of the application in the title bar. Otherwise, if a custom title is needed, it can be provided as the third parameter of the MsgBox function. The title can be a simple string. Here’s an example:
Sub Exercise22()
ActiveCell = MsgBox(“Your credentials have been checked” & _
“Your application has been authorized:” & _
“Congratulations!” & vbCrLf& _
“Before you leave, would you like to take our survey?”, _
vbYesNo Or vbQuestion, _
“Moments – Membership Application“)
End Sub
The result is shown in the following figure 15.
Figure 15
Note that the title is now customized rather than the application name. The title can also be a string created from an expression or a string emitted from a variable or value.
Return Value of the Message Box
The MsgBox function can be used to return a value that corresponds to the button the user clicked on the message box. Depending on the buttons displayed on the message box, after the user clicks, the MsgBox function can return a value. This value can be a member of the MsgBoxResult enumeration or a constant value recognized by the VisualBasic language. The returned value can be one of the following:
Figure 16
Input Box
The Visual Basic language provides a function that allows you to request information from the user, who can type information in the text field of the dialog box. The function used for this is called InputBox, and its basic syntax is:
InputBox(prompt[,title][, default][, xpos][, ypos][, helpfile, context])
Passing Messages
The most basic information that can be provided to the InputBox function is called the prompt. It should be a string that the user will read and know what is expected. Here’s an example:
Sub Exercise23()
InputBox (“Please enter your birth date, format yyyy-mm-dd”)
End Sub
The result is shown in the following figure 17.
Figure 17
When reading the message on the input box, the user is asked to enter a piece of information. The type of information the user should provide depends on the programmer. Therefore, two things should always be done. First, the user should be informed of the type of information requested: Is it a number (what type of number)? Is it a string (e.g., country name or customer name)? Is it the location of a file (e.g., C:\ProgramFiles\excelperfect)? Is the answer expected to be /No true/false type (if so, how should the user provide it)? Is it a date (if so, what format should the user enter)? These questions imply that a clear request should be expressed to the user, specifying the expected value. A sufficiently clear solution includes providing an example to the user.
Title of the Input Box
The second parameter of the InputBox function allows you to optionally specify the title of the input box, which is the string that will appear in the title bar. Since this is an optional parameter, if it is not passed, the input box will display the name of the application. Otherwise, to display your own title bar, pass the title parameter.
The title is passed as a string. Here’s an example:
Sub Exercise24()
ActiveCell = InputBox(“Please enter your birth date, format yyyy-mm-dd”,_
“Student Registration“)
End Sub
The result is shown in the following figure 18.
Figure 18
Note that the title is now customized rather than the application name. The title can also be a string created from an expression or a string emitted from a variable or value.
Default Value of the Input Box
Sometimes, even with a clear request, the user may not provide a new value but instead click “OK”. The problem is that you still need to get the value of the text box and may want to include it in an expression. This can be solved by filling the text box with a default value and providing an example to the user. To do this, the InputBox function provides a third parameter.
To provide an example or default value to the user, pass the third parameter to the InputBox function. If you want to use this parameter to provide an example that the user can follow, provide the correct format. Here’s an example:
Sub Exercise25()
ActiveCell = InputBox(“Enter student name: “, _
“Student Registration“, “Fan”)
End Sub
The result is shown in the following figure 19.
Figure 19
Note that when the input box displays a default value, that value is in the text box and has been selected. Therefore, if that value is acceptable, the user can accept it and click “OK”. Another way to use a default value is to provide a value that the user can accept; that is, the most common or likely value the user would enter. Here’s an example:
Sub Exercise26()
ActiveCell = InputBox(“Enter place of birth: “, _
“Student Registration“, “Wuhan“)
End Sub
The result is shown in the following figure 20.
Figure 20
Again, note that the user can accept that value and click “OK” or press the Enter key.
Position of the Input Box
By default, when the input box appears, it is displayed in the center of the screen. If needed, you can specify the position when the input box appears. To do this, the InputBox function is equipped with the fourth and fifth parameters. The fourth parameter specifies the x coordinate of the input box; that is, the distance from its left border to the left border of the display. The fifth parameter specifies the distance from the upper border of the input box to the upper border of the display.
Return Value of the Input Box
When the input box is displayed, after the user enters a value, they will click one of the buttons: OK or Cancel. If the user clicks “OK”, the value typed by the user should be obtained, and it should be determined whether the user typed a valid value. Since the InputBox function can return any type of value, it has no mechanism to validate user input. To get the value of the input box dialog when the user clicks “OK”, the return value of the InputBox function can be obtained.
After use, the InputBox function returns a string. Here’s an example:
Sub Exercise27()
Dim StudentName As String
StudentName = InputBox(“Enter student name: “, _
“Student Registration“)
MsgBox (“Student Name: ” & StudentName)
End Sub
You can also obtain any type of value from the input box. That is, when the InputBox function exits, due to the flexibility of the VisualBasic language, the compiler can directly convert the return value. Here’s an example:
Sub Exercise28()
Dim DateOfBirth As Date
DateOfBirth = InputBox(“Please enter your birth date, format yyyy-mm-dd”, _
“Student Registration“)
MsgBox (“Date of Birth: ” & DateOfBirth)
End Sub
Welcome to Knowledge Planet:PerfectExcelCommunity, for technical exchanges and questions, obtain more electronic materials, and join the special WeChat discussion group through the community for more convenient communication.
