Excel VBA: Show UserForm on Same Screen

Sharing is caring!

When creating Excel macros with VBA (Visual Basic for Applications), you often need to display a UserForm so users can interact with your code. But sometimes the UserForm opens in an awkward position, on another monitor or off-screen, making it inconvenient.

In this guide, we’ll show you how to display a UserForm on the same screen as your Excel window.

Understanding UserForms in Excel VBA

A UserForm in Excel VBA is a custom dialog box that lets you collect input, display information, or control the workflow of your macro. It’s like a mini application within Excel.

Common uses of UserForms include:

• Collecting user input through text boxes and dropdowns
• Displaying calculated results or status messages
• Allowing macro options selection before execution

By default, a UserForm appears in the center of the primary monitor, which might not be the same screen where your Excel workbook is open; especially on dual-monitor setups.

Why UserForms Open on a Different Screen

The main reason UserForms open in unexpected positions is that Excel VBA doesn’t automatically detect the monitor where your workbook is active. This often happens when:

• You have multiple monitors connected
• You drag Excel to the secondary screen but VBA still centers the form on the main screen
• The form’s StartUpPosition property is set to 1 - CenterOwner or 0 - Manual without proper positioning code

To fix this, you can write VBA code that positions the form relative to the Excel application window.

Key Properties for Positioning a UserForm

Excel VBA provides certain properties that control UserForm position:

To ensure the UserForm appears on the same screen as Excel, you need to calculate and set the Top and Left values.

How to Show a UserForm on the Same Screen

Step 1: Set StartUpPosition to Manual

Before writing the code, open the UserForm in the VBA editor and set:

1. Select your UserForm.
2. In the Properties Window, find StartUpPosition.
3. Change it to 0 - Manual.

This ensures that VBA will use your custom position instead of the default.

Step 2: Use VBA to Detect Excel Window Position

You can use the Application.Left and Application.Top properties to get the Excel window’s position, then adjust the UserForm’s position.

Private Sub UserForm_Initialize()
    Dim excelLeft As Long
    Dim excelTop As Long
    Dim excelWidth As Long
    Dim excelHeight As Long

    ' Get Excel application position and size
    excelLeft = Application.Left
    excelTop = Application.Top
    excelWidth = Application.Width
    excelHeight = Application.Height

    ' Position the UserForm in the center of Excel's window
    Me.Left = excelLeft + (excelWidth - Me.Width) / 2
    Me.Top = excelTop + (excelHeight - Me.Height) / 2
End Sub

What this does:

• Gets Excel’s position on the screen
• Centers the form within the Excel window
• Works even if Excel is on a secondary monitor

Step 3: Show the Form

To display the UserForm, use:

Sub ShowMyForm()
    UserForm1.Show
End Sub

When you run this macro, the form will now appear centered on the same screen as Excel.

Positioning the UserForm in a Specific Area

Sometimes you might want the UserForm not centered, but placed in a specific position—for example, aligned to the top right of the Excel window.

Private Sub UserForm_Initialize()
    Dim excelLeft As Long
    Dim excelTop As Long
    Dim excelWidth As Long
    
    excelLeft = Application.Left
    excelTop = Application.Top
    excelWidth = Application.Width

    ' Place form in top-right corner of Excel window
    Me.Left = excelLeft + excelWidth - Me.Width - 20
    Me.Top = excelTop + 20
End Sub

Use cases:

• Keeping the form visible while working in Excel
• Avoiding overlap with important workbook areas

Handling Different Screen Resolutions

If your users have different screen sizes or DPI scaling, you need to make the form position dynamic.

Using API Functions for Multi-Monitor Detection

Windows API calls can detect which monitor Excel is on and ensure the UserForm opens there.

Example with GetWindowRect:

Private Declare PtrSafe Function GetWindowRect Lib "user32" _
(ByVal hWnd As LongPtr, lpRect As RECT) As Long

Private Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

Private Sub UserForm_Initialize()
    Dim rectExcel As RECT
    GetWindowRect Application.hWnd, rectExcel
    
    Me.Left = rectExcel.Left + (rectExcel.Right - rectExcel.Left - Me.Width) / 2
    Me.Top = rectExcel.Top + (rectExcel.Bottom - rectExcel.Top - Me.Height) / 2
End Sub

This method:

• Works with multi-monitor setups
• Uses the actual window coordinates
• Avoids issues with scaling

Best Practices for UserForm Display

To ensure your UserForm looks professional and user-friendly:

• Test on different monitors to check positioning
• Keep form size reasonable to avoid cutting off controls
• Use relative positioning to adapt to different resolutions
• Avoid hardcoding pixel values unless necessary
• Ensure controls are aligned and have proper tab order

Example: Complete Macro for Same-Screen Display

Here’s a complete example combining all techniques:

' Place in UserForm code
Private Sub UserForm_Initialize()
    Dim excelLeft As Long
    Dim excelTop As Long
    Dim excelWidth As Long
    Dim excelHeight As Long

    excelLeft = Application.Left
    excelTop = Application.Top
    excelWidth = Application.Width
    excelHeight = Application.Height

    ' Center on same screen as Excel
    Me.Left = excelLeft + (excelWidth - Me.Width) / 2
    Me.Top = excelTop + (excelHeight - Me.Height) / 2
End Sub

' Place in a standard module
Sub ShowUserFormSameScreen()
    UserForm1.Show
End Sub

Troubleshooting Common Issues

Summary

By default, Excel VBA UserForms may appear on the primary monitor instead of the monitor where your Excel workbook is open. By setting the StartUpPosition to manual and using VBA code to calculate the position based on the Excel application window, you can ensure the form always opens on the same screen.

This approach works for multi-monitor setups, supports different resolutions, and makes your VBA applications more user-friendly.

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.