MsgBox function is a frequently used function in VBA. VBA applications use this function to communicate with the user. For example if a user entered incorrect input to the program then the program can notify it using msgbox function. Also this function is widely used to show the completion message to the users as well.
Below image shows a “Data saved!” message which was generated by msgbox function. In this sample application when the user fills the data and clicks the “Enter” button, program store them in the Excel database, clear the form and then display the “data saved!” message.
This is the code for the above example message box.
Various types of message box styles are available in VBA such as vbOKOnly, vbYesNo, vbCritical etc. In the above example I have used vbInformation. Once the message box is displayed, the user needs to click on a button to close it. So assume a user needs to enter hundreds of records through a form. Then if we use the above technique, the user will need to close the message box hundreds of times. We can increase the efficiency of the data entry process if we omit this kind of additional work. So can we display a notification to a user in VBA which doesn’t require any action from the user? Yes there is a way. So in this lesson you can learn how to create a notification which closes automatically after a predefined number of seconds after the display.
We are going to do this using Windows Scripting Host. Windows scripting host is a language independent scripting engine. Now let’s look at how to do this. First we need to declare a few variables.
Dim Message As Variant
Next we can set the duration.
You should assign the message showing duration in seconds. In this example the message is visible to the user only for 1 second. Now we can use the Windows scripting shell like this.
Below is the complete code to show the message only for 1 second
Dim Message As Variant
Duration = 1
Message = CreateObject("WScript.Shell").PopUp("Data saved", Duration, "")
"Data saved" Message will be displayed for one second and closed automatically. In the above example, the message doesn’t have any title. Because we have entered "" as the third parameter. Here is an example with the title.
Also in the above example, the message has only the “OK” button. But we can add the buttons according to our requirements. These are the types of buttons available.
vbOKOnly
vbOKCancel
vbYesNo
vbYesNoCancel
vbRetryCancel
vbAbortRetryIgnore
Following example shows how you can use vbAbortRetryIgnore.
You can add other buttons in a similar way. Few icons are also available to format the message box. They are as follows.
vbExclamation
vbInformation
vbCritical
vbQuestion
Following example uses vbYesNoCancel as the button and vbExclamation as icon.
