How to Keep a Modeless UserForm on Top in Excel VBA?

Sharing is caring!

If you’re working with UserForms in Excel VBA, you may have encountered a situation where you want to keep a modeless UserForm on top of other windows, even when the user interacts with Excel or other applications. This article will explain how to ensure your modeless UserForm always remains in the foreground, using simple VBA code. By the end, you’ll know exactly how to keep a UserForm visible and accessible at all times.

Understanding Modeless vs Modal UserForms

Before diving into the solution, it’s essential to grasp the difference between modal and modeless UserForms in Excel VBA, as this will help you determine when to use each type and how they affect user interaction and code execution.

• A modal UserForm is one that must be closed or hidden before the user can interact with any other part of the Excel application. When a modal UserForm is displayed, it essentially “locks” the application, preventing the user from accessing other worksheets, workbooks, or features until the form is dismissed. Code execution also pauses at the line where the modal UserForm is shown and resumes only after the form is closed.
• On the other hand, a modeless UserForm allows the user to freely interact with Excel and other applications while the form remains open and accessible. The code execution continues immediately after showing the modeless form, enabling the user to perform other tasks without being locked into the UserForm.

Here’s a quick comparison table to highlight the key differences:

To display a UserForm as modeless, you simply use the .Show method without specifying the vbModal argument:

UserForm1.Show

This line of code will display UserForm1 as a modeless form, allowing the user to interact with Excel while the form remains visible.

The Problem: Modeless UserForms Get Hidden

While modeless UserForms provide flexibility and allow users to multitask, they have a potential issue that can hinder their usability: if the user clicks on another Excel window, worksheet, or a different application, the UserForm gets hidden behind those windows. This can be frustrating and counterproductive if you want the UserForm to remain visible and easily accessible at all times, especially if it contains important information or frequently used tools.

The Solution: Use the Windows API to Keep the UserForm on Top

To overcome the issue of modeless UserForms getting hidden, we can leverage the power of the Windows API to set the form’s “z-order”. The z-order determines the stacking order of windows on the screen, with windows higher in the z-order appearing on top of those lower in the order.

Step 1: Declare the Necessary Windows API Constants and Function

The first step in implementing the solution is to declare the required API constants and function in a standard module in your VBA project. These declarations define the values and function signature we’ll use to interact with the Windows API.

Public Const HWND_TOPMOST = -1
Public Const SWP_NOSIZE = &H1
Public Const SWP_NOMOVE = &H2
Public Const SWP_NOACTIVATE = &H10

Public Declare PtrSafe Function SetWindowPos Lib "user32" _
    (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, _
    ByVal X As Long, ByVal Y As Long, ByVal cx As Long, _
    ByVal cy As Long, ByVal uFlags As Long) As Long

Let’s break down the constants and the function:

• HWND_TOPMOST: This constant specifies that the window should be placed at the top of the z-order, above all non-topmost windows. It ensures that our UserForm will remain on top of other windows.
• SWP_NOSIZE, SWP_NOMOVE, and SWP_NOACTIVATE: These constants are flags that control the behavior of the SetWindowPos function. They indicate that the function should not change the window’s size, position, or activation state.
• SetWindowPos: This is the Windows API function that allows us to change the z-order and other properties of a window. It takes several parameters, including the window handle (hwnd), the position in the z-order (hWndInsertAfter), and various flags to control its behavior.

Step 2: Create a “SetTopMost” Sub

Now that we have the necessary declarations, we can create a subroutine called SetTopMost that will call the SetWindowPos API function with the appropriate arguments to set our UserForm’s z-order position to HWND_TOPMOST.

Public Sub SetTopMost(hwnd As LongPtr)
    SetWindowPos hwnd, HWND_TOPMOST, 0, 0, 0, 0, _
        SWP_NOMOVE Or SWP_NOSIZE Or SWP_NOACTIVATE
End Sub

This subroutine takes a single parameter, hwnd, which represents the window handle of the UserForm we want to keep on top. Inside the sub, we call the SetWindowPos function, passing the following arguments:

• hwnd: The window handle of the UserForm.
• HWND_TOPMOST: The position in the z-order where we want to place the UserForm (at the top).
• 0, 0, 0, 0: These values indicate that we don’t want to change the window’s position or size.
• SWP_NOMOVE Or SWP_NOSIZE Or SWP_NOACTIVATE: A combination of flags that tell the function not to modify the window’s position, size, or activation state.

Step 3: Call SetTopMost When Showing the UserForm

With the SetTopMost subroutine in place, the final step is to call it immediately after showing the modeless UserForm. This ensures that the UserForm is set to be always on top right from the start.

UserForm1.Show
SetTopMost UserForm1.hwnd

In this code snippet, we first show UserForm1 using the .Show method, which displays it as a modeless form. Then, we call the SetTopMost sub, passing the UserForm’s window handle (obtained via the .hwnd property) as the argument.

And that’s it! Your modeless UserForm will now remain on top of Excel and other application windows, ensuring that it’s always visible and easily accessible to the user.

Bonus Tip: Use a Timer to Repeatedly Set the UserForm on Top

In some cases, you may find that the UserForm still gets hidden behind other windows, especially if the user is actively interacting with other applications or if certain programs deliberately override the z-order settings. To handle such situations, you can employ a VBA timer to periodically call the SetTopMost subroutine at a specified interval, ensuring that the UserForm remains on top even if it gets temporarily hidden.

Here’s how you can implement this bonus tip:

1. Add a Timer control to your UserForm from the toolbox. This control will allow you to execute code at regular intervals.
2. Set the timer’s Interval property to the desired number of milliseconds between each timer event. For example, you can set it to 500 to trigger the event every half a second.
3. In the timer’s Timer event, call the SetTopMost subroutine, passing the UserForm’s window handle as the argument:

Private Sub Timer1_Timer()
    SetTopMost Me.hwnd
End Sub

4. To start the timer when the UserForm is shown, add the following line to the UserForm’s Activate event:

Private Sub UserForm_Activate()
    Timer1.Enabled = True
End Sub

5. To stop the timer when the UserForm is hidden or closed, add the following line to the UserForm’s Deactivate event:

Private Sub UserForm_Deactivate()
    Timer1.Enabled = False
End Sub

With this setup, the UserForm’s z-order will be regularly updated by the timer, ensuring that it stays on top even if the user interacts with other windows or if other programs try to override the z-order settings.

Considerations and Limitations

While keeping a modeless UserForm on top can be incredibly useful in certain scenarios, it’s important to consider the following aspects before implementing this technique in your VBA projects:

• User experience: Always strive for a good balance between functionality and user-friendliness. An always-on-top UserForm might be helpful in some cases, such as displaying a tool palette or real-time data, but it can also be annoying or distracting in other situations. Consider your users’ needs and preferences, and use this technique judiciously to enhance their experience, not hinder it.
• Performance: Continuously calling the Windows API to set the z-order, especially when using a timer with a short interval, may slightly impact the performance of your VBA application. While the impact is usually negligible, it’s worth keeping in mind, particularly if you have multiple always-on-top UserForms or if your application is already resource-intensive. Consider adjusting the timer interval or using the technique sparingly to minimize any potential performance issues.
• Compatibility with other applications: Some applications, particularly games, full-screen programs, or specialized software, may deliberately override the z-order settings to ensure their windows remain on top. In such cases, your always-on-top UserForm might still get hidden behind these windows, despite your best efforts. Be aware of this limitation and consider alternative solutions or workarounds if compatibility with specific applications is a crucial requirement for your project.

Final Thoughts

Keeping a modeless UserForm on top in Excel VBA is a handy technique when you need the form to remain visible and accessible at all times. By leveraging the Windows API and optionally using a timer, you can ensure that your UserForm stays in the foreground, enhancing the user experience and improving the functionality of your VBA application. Just remember to consider the potential impact on user experience and performance, and use this technique wisely.

FAQs

Private Sub Timer1_Timer()
    If AlwaysOnTop Then SetTopMost Me.hwnd
End Sub

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.