Excel VBA: Modeless MsgBox (Create with 3 Ways)

With the help of Excel VBA (Visual Basic for Applications), users can automate processes and design unique solutions. Messages to users during the execution of macros or processes are a frequent necessity in Excel VBA projects. A built-in VBA function called MsgBox enables programmers to show message boxes with editable text and buttons. The MsgBox function, on the other hand, is modal by default, which prevents the user from interacting with the workbook until the message box is closed. You will learn to create a Modeless MsgBox in Excel VBA in this article.

This modal behavior may occasionally cause users problems or inconvenience, particularly if the macro or process requires frequent user input or takes a long time to complete. To fix this, we can use the modeless message box in Excel, which enables us to work on the worksheet regardless of whether the message is still open. In this article, we’ll explore the idea of modeless MsgBox, its mechanism, and some best practices for utilizing it in real-life problems. So, let’s be with us till the end.

Introduction to MsgBox in Excel VBA

A message box or MsgBox, is one type of dialog box that displays a box containing an iconic statement as well as a few command buttons. Simply put, we use the MsgBox function in VBA to create and display it.

Generally, a message box has four components: Title, Prompt, Button, and Close Icon. We’ve seen it in the above image.

• Syntax:

MsgBox(Prompt, [Button As VbMsgBoxStyle = vbOkOnly], [Title], [HelpFile], [Context]) As VbMsgBoxResult

• Arguments:

We can use message boxes for various purposes. Some of them are the following:

• We can use message boxes to confirm user activities such as data deletion or file changes. The message box can prompt the user to confirm the activity or allow them to cancel it.
• People use message boxes to convey feedback to the user, such as presenting the result of a computation or confirming the completion of an activity.
• Message boxes can present aid info to the user, such as instructions on how to utilize a particular feature or how to complete a job.

Overall, the message box is very beneficial for all kinds of Excel users.

What Is Modeless or Non-Modal in VBA?

From the above video, we can see that we are able to click on other parts outside the message box while it remains open. Normally, we cannot do that to a typical message box.

“Modeless” or “non-modal” user interface windows in VBA allow the user to communicate with the application or worksheet while the window is still open. A modeless window, in other terms, does not prevent the user from reaching other portions of the software or spreadsheet. It requires the user to respond to the dialog box before continuing with other actions. Before continuing to utilize the application or spreadsheet, the user must either close the window or respond. In this respect, a modeless or non-modal dialog box is different from the default modal one.

Excel VBA Modeless MsgBox: Create with These 3 Ways

For ease of understanding, we are going to use the Marks of Students in the Aptitude Test of a particular institute. This dataset includes the ID, Name, corresponding Marks, and Grade in columns B, C, D, and E respectively.

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

Now, we’ll utilize this dataset to create and visualize modeless message boxes using VBA in Excel. So, let’s explore them one by one.

Not to mention, here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.

1. Incorporating Private Function to Generate Modeless MsgBox Without UserForm

In this scenario, we added a Command Button to our sheet. Whenever we click on this button, it displays a message box with a specific command line in it. Now, we can interact outside the box, though it’s still open. Also, we can select any cell or range as we wish. So, we can declare it as a modeless message box in Excel.

At first, we have to insert the button in our worksheet. Follow the sequence to place it in your desired position: Developer >> Insert Controls on the Controls group >> Command Button (ActiveX Control). After that, place the button in your preferred location with the plus-shaped mouse cursor.

To change the caption of this button, first, we have to keep the Design Mode turned on. It’s nothing complex; just click on the respective icon in the Controls group of commands, as shown in the above image. This mode enables us to modify and personalize the controls we added to our sheet.

Now, right-clicking on the button displays the Context Menu, where we can find our desired option “Properties”.

It helps us show the Properties Window. This window shows all the properties of the selected object, and we can also change them from here. From the Caption option, we can change the button’s display name. In our case, we altered it to Show MsgBox.

In this situation, we’ve to assign the working code to the button. That means when we click on the button, that code will execute. To do this, double-click on the button. Instantly, it’ll add a code module to our respective sheet, which is Sheet2 (Private Function).

This is the code we used for the button. You can copy it and use it in your own workbook.

' Use the "PtrSafe" keyword to make sure the code _
runs on both 32-bit and 64-bit versions of Excel
Private Declare PtrSafe Function MessageBox _
Lib "User32" Alias "MessageBoxA" (ByVal hWnd As LongPtr, _
    ByVal lpText As String, _
    ByVal lpCaption As String, _
    ByVal uType As Long) As Long
Private Sub CommandButton1_Click()
' Declare a variable to store the user's response
Dim Response As Long
' Display a message box with a Yes/No option, _
and make it system-modal (so the user can interact _
with Excel while making a choice)
Response = MessageBox(&O0, "Do you want to copy the range?", _
"My Question", vbYesNo + vbSystemModal)
' Check the user's response
If Response = vbYes Then
    ' Display a message box with instructions to copy the range, _
    and make it system-modal
    Response = MessageBox(&O0, _
    "Click OK and then select the range and press CTRL + C", _
    "Command", vbOKOnly + vbSystemModal)
Else
    ' Display a message box telling the user to close _
    the dialog box, and make it system-modal
    Response = MessageBox(&O0, "Close the box", _
    "Command", vbOKOnly + vbSystemModal)
End If
End Sub

As we can clearly see in the video above, while the message box is open, we can click elsewhere on the sheet, and the message box isn’t going away either. So, it’s very advantageous for users to see and engage with the worksheet while they are getting instructions from the dialog boxes.

Here, we can see that we have to deal with a total of 3 message boxes. In the first box, it asks for our opinion, and based on our response, it gives two different boxes for two separate attempts. Actually, this message box passes the rules to copy a range in a worksheet. So, our main objective should be, able to select and copy a range in Excel. But here, we can see that, while the message box is opened and showing the instructions, we can select any cell or range in our sheet.

Moreover, we can click on any option on the ribbon. But we cannot achieve our main goal, which is copying. After selecting the range, whenever we try to copy it using the Copy command in the Clipboard group on the Home tab ribbon, it doesn’t work. This option is greyed out.

Also, we couldn’t use the keyboard shortcut CTRL + C without closing the message box. So, it becomes a limitation to our first method for this purpose.

Read More: VBA MsgBox Format in Excel

2. Creating Customized Modeless MsgBox with VBA UserForm in Excel

Instead of using the built-in MsgBox function, customizing a modeless message box with a UserForm offers a number of benefits. Here, it starts by clicking on the button shown in step 1 in the overview image.

When you click on it, the message box that comes up is actually a UserForm. And the main attraction of it is that it’s a modeless message box. So, unlike the built-in MsgBox function, which pauses code execution until the user responds, modeless message boxes (UserForm) permit users to interact with the Excel workbook while the message box is visible.

You can see we selected the B6:C10 range, and also copied it, which was unavailable in the past method.

Firstly, in the VBA Editor, click on Insert >> UserForm to create a new user form. Immediately, a blank new UserForm is inserted at the right empty place on the display.

Along with the UserForm, a Toolbox is viewable. Using this box, we can add different Controls to our form. For our task, we inserted a Label control and a CommandButton control.

We can easily change the caption of each component from the Properties Window at the left part of the display. We changed this property of our UserForm, Label, and CommandButton.

As an example, we changed the caption of the UserForm to “Want to Copy Range?” and of the CommandButton to “OK”. To do this, just click on this element and then, write your preferred name in the Caption section of the Properties Window.

If you face difficulty understanding the picture above, you can follow the following video clip for easier demonstration.

Accidentally, if your PC isn’t showing the Properties Window, you can enable it with some easy clicks. Just go to the View tab in the VBA editor, and click on the Properties Window option. Right away, it’ll be watchable whenever you select any object.

In this instance, right-click on the OK button and select the View Code option to incorporate the working code into this button.

The code associated with the OK button is as follows:

Private Sub CommandButton1_Click()
    Unload Me
End Sub

In fact, when a user presses “CommandButton1,” this code is meant to close the UserForm. When the UserForm is no longer required and needs to be deleted from the screen to free up memory space, this can be helpful.

The code connected to the Command Button (Want to Copy?) of the sheet is like the following:

Private Sub CommandButton1_Click()
    UserForm1.Show vbModeless
    UserForm1.Label1.Caption = "Select the Range and press CTRL + C."
End Sub

This code is quite simple. It shows the UserForm we just created. But the trick is, it shows it in the modeless type using the vbModeless property. So, we can easily interact with the other part of the worksheet while the message box is opened. Also, we set the caption of the Label control of our form to the message which we want to show when the form is displayed.

You can see that we can select the cells, as well as select the B4:C8 range in our worksheet and copy them also while the message box (UserForm) is open. Which is the main convenience we get from this method.

Also, we pasted the copied range in the B20:C24 range with the message box opened. So, you can get the full leverage of using a modeless message box if you accomplish your task by following this particular method.

Read More: Create VBA MsgBox Custom Buttons in Excel

3. Constructing VBA Modeless MsgBox with UserForm Manually

In this method, we are actually counting the total marks in the selected range. But here the difference is that the message box shows different information after executing each step of the code.

It takes the mark of the first student and adds it with zero and shows 10% completion, then after 2nd execution, it shows 20% completion in the message box. And the amazing fact is that you can click or interact anywhere on the sheet while the code is running. So, let’s get deeper into this.

You have to insert another UserForm like the previous method. Then, in the Properties Window, set the ShowModal property to False. In fact, it works the same as the vbModeless property in the code.

The code associated with the OK button of the UserForm is the following:

Private Sub CommandButton1_Click()
 Unload Me
End Sub

So, clicking on this button unloads the UserForm from the memory and erases it from the display.

Here’s the code script available for your work.

Sub Modeless_MsgBox_Manually()
    Dim i As Long
    Dim total As Double
    Dim start_Time As Double
    Dim elapsed_Time As Double
    Dim progress_Form As New UserForm2 'change UserForm2 to the name of your UserForm
    'you need to select the range before executing this code
    Set Rng = Selection
    'show the progress form
    progress_Form.Show (False) 'set the argument to False to make the form modeless
    'start the timer
    start_Time = Timer
    For i = 1 To 10
        'perform a portion of the calculation
        total = total + Rng.Cells(i, 1).Value
        'update the progress text on the UserForm
        progress_Form.Label1.Caption = "Progress: " & i & "0% complete"
        'refresh the UserForm to display the new progress text
        DoEvents
        'creates a one second delay in the code execution
        Application.Wait Now + TimeValue("00:00:01")
    Next i
    'stop the timer
    elapsed_Time = Timer - start_Time
    'show the total and elapsed time on the UserForm
    progress_Form.Label1.Caption = "The total is " & total & vbCrLf & "Elapsed time: " & Format(elapsed_Time, "0.00") & " seconds."          
End Sub

Here, it’s visible that we selected the D5:D14 range first and then ran the code. The code is adding the marks one by one and shows the progress percentage in a status message box. And, while it’s working, we can select anything in the worksheet. After execution, it shows the total in the message box. Also, the elapsed time is visible.

Read More: VBA MsgBox to Return Values in Excel

Understanding Excel VBA UserForm ShowModal Property

UserForm in VBA is a special dialog box or form that can be made to communicate with users. A crucial component of the UserForm that controls how the form is shown to the user is the ShowModal attribute. From the previous sections, we must know that we can control this property from the Properties Window of the UserForm. Either we can set it to True or False.

When the ShowModal attribute is set to True, the UserForm is presented as a modal dialog box, requiring user interaction before moving on to other application activities. As a result, the form becomes an obstruction that inhibits users from interacting with other areas of the application until the form has been fully used. Modal dialog windows can be helpful in circumstances when the user must enter data or make a choice that impacts the way the application behaves.

When the ShowModal attribute is set to False, the UserForm is presented as a modeless form, allowing the user to continue using the Excel application while the form is visible. For presenting information like alarms, status updates, or progress indicators without interfering with the user’s workflow, modeless forms can be helpful.

In conclusion, the ShowModal property of the VBA UserForm controls whether the form is presented as a modal dialog box or a modeless form, and the decision of which to employ should be based on the application’s unique requirements as well as the desired user experience.

Advantages of Using Modeless MsgBox in Excel

A modeless message box may be helpful when the user wants to get alerts or information while still being able to work on other portions of the application or spreadsheet. We can show a progress bar or a status message, for instance, in a modeless window while a time-consuming job is being completed in the background. While the job is running, the user can still interact with other areas of the program.

1. Ease of Operation

Users can still utilize the application while a modeless message box is being displayed. This lessens frustration and enhances the user experience.

2. Enhanced Productivity 

By enabling users to finish work while the message box is visible, modeless message boxes can enhance productivity.

3. Improved Accessibility

Users who would have trouble using modal message boxes, such as users with vision impairments or motor difficulties, may find modeless message boxes to be more accessible.

4. Flexibility

We can imply modeless message boxes for a range of purposes, including displaying error messages, giving status updates, and displaying progress bars.

In general, modeless message boxes offer a more versatile and consumer-friendly way to interact with users and share information while still enabling them to use the service.

Limitations of Modeless MsgBox in Excel VBA

While employing modeless message boxes in Excel has several benefits in terms of increasing user experience, they do have some drawbacks too:

1. Restricted User Caution

Users who may be focused on other regions of the spreadsheet may easily ignore or miss modeless message boxes. This may cause crucial information to be missed.

2. Threat of Abuse

The overuse of modeless message boxes can irritate and exhaust users. Users may grow irritated and start to disregard message boxes altogether if they are overloaded with them.

3. Decreased Lucidity

Modeless message boxes do not obstruct the user’s workflow like modal message boxes do, which require users to respond to them before moving on to other tasks. Because of this, it could be challenging to communicate the message’s urgency or relevance.

4. Technical Restrictions

The utilization of modeless message boxes may be subject to technical restrictions, depending on the precise version of Excel being used. As an example, certain older versions of Excel may not enable the usage of modeless message boxes at all.

Modeless message boxes can be helpful in some circumstances, but before deciding to use them in Excel, it is vital to take into account their restrictions and possible impact on the user experience.

What to Do If vbModeless Is Not Working in Excel VBA?

There are multiple possible reasons why the vbModeless, one of the settings for the Show method in Excel, might not be functioning, as well as various troubleshooting techniques you can use to fix the problem:

1. Go Over UserForm Properties

The UserForm in question should have “ShowModal = False” set as one of its properties. The form will appear as a modal dialog box and prohibit the user from interacting with other areas of the application if “ShowModal = True” is set.

2. Verify Code

Check to make sure the ShowModal property isn’t accidentally set to True by the code that initiates the UserForm. Make sure that no event handlers or macros that may be launching the form are overriding the ShowModal property.

3. Inspect for Incompatible Code

Make sure that no other add-ons or pieces of code are interfering with the UserForm‘s ShowModal attribute. This might involve background-running VBA code from other programs or add-ins that could be modified how the UserForm behaves.

4. Try on Different Computer

If the problem still exists, try opening the Excel file on a different PC to determine whether the issue is unique to your computer or the Excel installation.

5. Restart Excel

Rebooting Excel can sometimes fix issues with the ShowModal attribute of the UserForm.

6. Upgrade Excel

Verify that Excel is running at the most recent version and that any applicable updates or patches have been made available. There can be known problems with older Excel versions that are fixed in more recent ones.

Things to Remember

• You can write MsgBox “Your message here”, vbSystemModal in your code to show a modal message box. Also, you could use vbApplicationModal in that place. But you cannot use MsgBox “Your message here”, vbModeless because this property isn’t available till now for the default MsgBox function in Excel.
• Always make sure to turn on the Design Mode while editing on the Command Button.

Frequently Asked Questions

1. How do you close a modeless MsgBox in Excel VBA?

In fact, we use the UserForm to create a resemblance of a modeless message box. Generally, there is a close icon in every UserForm. After working on the form, you can simply click on this icon to erase the form. Moreover, you can add a CommandButton in the form and apply a simple code to it to close the form. Just write Unload Me in this code which unloads the current object from the memory.

2. Can you customize the appearance of a modeless MsgBox in Excel VBA?

Yes, you can alter a modeless message box’s appearance in Excel VBA by constructing a UserForm object that resembles a message box and changing its properties to get the look you want.

3. How do you capture the user’s response to a modeless MsgBox in Excel VBA?

You can add Controls to the UserForm (such as CommandButton) and make event handlers for those controls to record the user’s action and save the result in a variable in order to capture the user’s response. An illustration of how to make a straightforward modeless message box with a “Yes” and “No” button and record the user’s input is given below:

' Create a custom modeless message box. In place of "UserForm1", write the name of your UserForm
UserForm1.Show vbModeless
' Declare a variable to store the user's response
Dim response As String
' Handle the Click event for the Yes button
Private Sub bttnYes_Click()
    response = "Yes"
    UserForm1.Hide
End Sub
' Handle the Click event for the No button
Private Sub bttnNo_Click()
    response = "No"
    UserForm1.Hide
End Sub
' Check the value of the response variable to determine the user's selection
If response = "Yes" Then
    ' Do something if the user clicked Yes
ElseIf response = "No" Then
    ' Do something if the user clicked No
End If

That’s how you can easily store the user’s response and carry the remaining works based on them.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Conclusion

Ultimately, the Modeless MsgBox in Excel VBA can become a great feature for those who don’t want any interruption in their flow of work. This can be especially helpful for lengthy macros or procedures that call for user input or approval.

Here, in this reference, we included 3 methods with 3 different types of tasks. We’ve done the first one by creating a private message box function, and the second and third ones with the help of building a UserForm. We think that the last one is more interesting than the other two.

Also, we discussed the advantages, limitations, and when and where to use these modeless message boxes in Excel. Moreover, we mentioned the ShowModal property of the UserForm in detail. Don’t forget to go through the last two sections Things to Remember and FAQs for some important insights which we faced while working on this topic. That’s all from us.

Don’t forget to download the practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

Related Articles

• How to Use Excel VBA MsgBox Title
• Excel VBA: Work with Variable in MsgBox
• Excel VBA: Show Multiple Values with Multiple Variables in MsgBox
• How to Use MsgBox and InputBox in VBA Excel