VBA & Macro Problems

How to Create Excel VBA Macros for Worksheet and Workbook Switchers

Maira Mehtab Sep 20, 2026 869 views

Question details

The user wants to create custom keyboard shortcuts to activate worksheet and workbook switchers using VBA, requiring support for typing the first letter to navigate and handling hidden sheets.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Navigating quickly between multiple worksheets and workbooks using customized VBA macros and keyboard shortcuts.
Observed behavior: Excel lacks a direct VBA command to trigger the native right-click sheet-tab scroller menu, requiring the use of a custom UserForm and specific dialog commands.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that your macro security settings allow VBA macros to execute.

Solution 1Recommended

Use Application.Dialogs for a Workbook Switcher

Utilize Excel's built-in dialog command to quickly bring up a window for switching between active workbooks.

You can call a native Excel dialog through VBA to switch between open workbooks. However, keep in mind that this method will also display any hidden sheets within those workbooks.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications editor.

Insert a New Module

Go to Insert > Module to create a blank script window.

Enter the VBA Code

Type the following code: Sub ShowWorkbookSwitcher() Application.Dialogs(xlDialogActivate).Show End Sub

Assign a Keyboard Shortcut

Return to Excel, press Alt + F8 to open the Macro dialog, select 'ShowWorkbookSwitcher', click 'Options', and assign your preferred shortcut key.

Hidden Sheets Visibility: The xlDialogActivate dialog will display hidden sheets. If this is an issue, a custom UserForm will be required to filter them out.

Solution 2

Create a Custom Worksheet Switcher UserForm

Build a custom UserForm to switch worksheets, allowing for advanced navigation like typing the first letter of the sheet name.

Advanced Macro Support

Create Macros and Manage Sheets Easily with WPS Office

WPS Office provides robust VBA and macro support, allowing you to create custom UserForms, assign shortcuts, and navigate complex workbooks seamlessly.

1. Enable Developer Tools: Open WPS Spreadsheet, navigate to the settings, and enable the Developer tab to access VBA features.
2. Launch the VBA Editor: Click 'Visual Basic' in the Developer tab or press Alt + F11 to start writing your custom switcher scripts.
3. Run Custom Macros: Assign custom shortcuts to your macros directly within the WPS interface to streamline your workflow.

Full support for VBA and custom UserFormsHighly compatible with Microsoft Excel macro formats (.xlsm)Lightweight architecture for faster macro executionIntuitive tab management interface for heavy workloads

Frequently Asked Questions

Can I use VBA to directly open the right-click sheet-tab scroller menu?

No, Excel does not provide a built-in VBA command that directly triggers the native right-click sheet-tab scroller menu. Creating a custom UserForm is the recommended workaround.

How do I assign a custom keyboard shortcut to my VBA macro?

In your spreadsheet, press Alt + F8 to open the Macro dialog box. Select your desired macro from the list, click the 'Options' button, and enter the letter you want to use for your shortcut key.

Why does my workbook switcher macro generate an error?

Errors can occur if your workbook state prevents dialogs from opening (e.g., a protected workbook without visible windows), if the VBA syntax is incorrect, or if your Excel macro security settings are blocking the script execution.

Excel & Data Problems