How to Reposition an Excel VBA CommandBar Popup Window
Question details
The user wants to set a specific screen position, such as the center of the window, for a built-in Excel VBA CommandBar popup window used for worksheet navigation.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Navigating between numerous worksheets (30+) using a popup menu assigned to a Quick Access Toolbar button.
- Observed behavior
- The built-in CommandBars.ShowPopup method natively lacks reliable parameters to define specific screen coordinates, making it difficult to control the popup's screen position directly via VBA.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have the Developer tab enabled to access the Visual Basic editor and insert UserForms.
Create a Centered Custom UserForm for Worksheet Navigation
Since Excel controls the built-in CommandBar popup position automatically, creating a custom UserForm with a ListBox provides precise control over its screen location and behavior.
The CommandBars.ShowPopup method natively lacks absolute coordinate positioning in VBA. It generally defaults to appearing where the macro was triggered, such as near the Quick Access Toolbar button.
A custom UserForm circumvents this limitation. By utilizing the StartUpPosition property, you can force the navigation menu to open precisely in the center of the application window, regardless of where the cursor is located.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
Click 'Insert' in the top menu and select 'UserForm'. Using the Toolbox, draw a 'ListBox' control onto the new form to hold your worksheet names.
Select the UserForm. In the Properties window on the left, locate the 'StartUpPosition' property and change it to '1 - CenterOwner'. This ensures it always opens in the middle of the Excel window.
Right-click the UserForm and select 'View Code'. Use the 'UserForm_Initialize' event to loop through the workbook and populate the ListBox with the names of all visible worksheets.
In the same code window, add code to the ListBox's 'Click' or 'DblClick' event to execute 'Worksheets(ListBox1.Value).Activate', followed by 'Unload Me' to close the form after selection.
Run VBA UserForms Seamlessly with WPS Office
WPS Office provides robust support for Excel VBA, allowing you to create, edit, and run custom UserForms and CommandBar macros just as you would in Microsoft Excel. It offers a familiar interface and comprehensive macro compatibility to streamline your workbook navigation.
- 1. Enable Developer Tools: Open your spreadsheet in WPS Office, navigate to the Options menu, and ensure the Developer tab is enabled.
- 2. Open the VB Editor: Click the Developer tab and select 'VB Editor' (or press ALT + F11) to access the integrated VBA coding environment.
- 3. Insert UserForms: Use the exact same VBA methods to insert UserForms, customize properties like StartUpPosition, and execute your navigation macros instantly.
Frequently Asked Questions
Can I use Windows API to position CommandBars.ShowPopup?
While advanced users sometimes use Windows API functions like GetCursorPos or SetCursorPos to simulate cursor placement before calling ShowPopup, it is highly unstable across different display resolutions and scaling settings. A UserForm is much more reliable.
Why doesn't the CommandBar popup show exactly where I click?
By default, CommandBars.ShowPopup attempts to render at the current mouse cursor location. If you trigger the macro via a Quick Access Toolbar button, the popup will appear near the top-left of the screen rather than in the center of the sheet.
How do I assign my custom UserForm macro to a button?
First, write a simple subroutine in a standard module containing the command 'UserForm1.Show'. Then, right-click the Quick Access Toolbar, select 'Customize Quick Access Toolbar', choose 'Macros' from the dropdown, and add your new subroutine to the toolbar.
