How to Make a VBA VBE Toolbar Persist After Excel Restarts
Question details
The user needs a method to ensure a custom VBA Editor (VBE) toolbar remains permanently available after closing and reopening the spreadsheet application.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Developing a custom toolbar for the Visual Basic Editor using a VBA script and wanting it to stay docked or floating across different software sessions.
- Observed behavior
- A VBE toolbar created manually remains available after the application closes, but a toolbar created programmatically using VBA disappears completely when the application is restarted.
Ensure you have Developer tools enabled in your spreadsheet application and verify that your macro security settings permit VBA scripts to execute upon startup.
Automatically Recreate the VBE Toolbar on Startup
Since VBA-generated VBE toolbars are not permanently saved by the application, the best practice is to recreate them automatically every time the workbook or add-in loads.
Even when using the Temporary:=False argument, command bars created via VBA in the VBE are typically treated as session-specific. To bypass this limitation, you can execute the toolbar creation script whenever the application starts.
Press ALT + F11 to launch the Visual Basic Editor in your application.
In the Project Explorer, double-click 'ThisWorkbook'. Select 'Workbook' from the left dropdown and 'Open' from the right dropdown to generate the Private Sub Workbook_Open() event.
Add your Application.VBE.CommandBars.Add code inside this event. Ensure you include an Error Handler or a loop to check if the toolbar already exists to avoid duplicating it.
Save your file as an Excel Add-in (.xlam). Go to Developer > Excel Add-ins, browse for your saved file, and enable it so the code runs automatically on every startup.
Create the Toolbar Manually in the VBE Interface
Toolbars created manually through the VBE user interface are stored permanently in the application's configuration files and naturally persist across restarts.
Switch to WPS Office for Seamless Spreadsheet Management
If you are frustrated by specific VBA quirks and heavy application limitations, consider trying WPS Office. It provides a lightweight, highly compatible alternative with robust features, familiar interfaces, and excellent handling of your everyday spreadsheet tasks.
- 1. Download the Installer: Visit the official WPS Office website and click the free download button.
- 2. Install the Application: Run the setup file and follow the on-screen instructions to install the suite on your device.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing files to instantly resume your work with full compatibility.
Frequently Asked Questions
Why does the Temporary:=False argument not work for VBE CommandBars?
In the Visual Basic Editor environment, command bars created programmatically are generally treated as temporary session elements by default, often overriding the Temporary argument. Auto-recreating them on startup is the most reliable workaround.
How do I prevent duplicate toolbars from appearing every time the application starts?
Before executing the CommandBars.Add method in your startup script, loop through the Application.VBE.CommandBars collection to check if a toolbar with your custom name already exists. If a match is found, skip the creation step.
Can I easily share my custom VBE toolbar setup with other users?
Yes. If you script the toolbar creation inside the Workbook_Open event of an Add-in (.xlam) file, you can simply share that file. When other users install the Add-in, the toolbar will be automatically built in their VBE environment.
