Excel VBA: Modeless MsgBox (Create with 3 Ways)

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. You can see 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:

• 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.

What Is Modeless or Non-Modal in VBA?

From the above video, you 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 in 3 Ways

We have 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. We’ll utilize this dataset to create and visualize modeless message boxes using VBA in Excel.

Method 1 – Incorporating a Private Function to Generate a Modeless MsgBox Without a UserForm

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.

• Go to the Developer tab.
• Select Insert Controls on the Controls group and choose Command Button (ActiveX Control).
• Place the button in your preferred location with the plus-shaped mouse cursor.

To change the caption of this button, keep the Design Mode turned on.

• Right-click on the button and select the option Properties.

This window shows all the properties of the selected object, and we can also change them from here.

• From the Caption option, change the button’s display name. In our case, we altered it to Show MsgBox.
• Double-click on the button. You’ll get a new code module to the sheet, which is Sheet2 (Private Function).

• Copy this code and paste it into the module:

' 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

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

Method 2 – Creating a Customized Modeless MsgBox with a 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.

• In the VBA Editor, click on Insert and select UserForm. A blank new UserForm is inserted in the display.

• You’ll also get a Toolbox control panel. Add different Controls including a Label and a CommandButton.

• Change the captions of the new elements through their Properties.

We changed the caption of the UserForm to “Want to Copy Range?” and of the CommandButton to “OK”.

• If your PC isn’t showing the Properties Window, go to the View tab in the VBA editor and click on the Properties Window option.

• 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

When a user presses “CommandButton1,” this code is meant to close the UserForm.

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

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

• We can select the cells and the select the B4:C8 range in our worksheet and copy them while the message box (UserForm) is open.
• We pasted the copied range in the B20:C24 range with the message box opened.

Read More: Create VBA MsgBox Custom Buttons in Excel

Method 3 – Constructing a VBA Modeless MsgBox with the UserForm Manually

We’ll count the total marks in the selected range. 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.

• Insert a UserForm like in the previous method.
• In the Properties Window, set the ShowModal property to False.

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

Private Sub CommandButton1_Click()
 Unload Me
End Sub

Clicking on this button unloads the UserForm from the memory and erases it from the display.

• Here’s the code you need to enter in the VBA module:

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

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.

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 a Modeless MsgBox in Excel

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 a Modeless MsgBox in Excel VBA

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?

Fix 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.

Fix 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.

Fix 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.

Fix 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.

Fix 5 – Restart Excel

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

Fix 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

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.

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.

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

You can store the user’s response and carry the remaining works based on them.

Download the Practice Workbook

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