Developer FAQ - CommandBars
Note: This document was written for Excel 97 - Excel 2000
I have a macro attached to a toolbar button. Is it possible to have the macro perform a different action if the user presses Shift while the button is clicked?
Yes, but you have to use a Windows API call to detect if the Shift key is pressed.
Excel 95 had a handy menu editor, but it's missing in Excel 97 and Excel 2000. What gives?
Beginning with Excel 97, the toolbars and menus in Excel are entirely different. Both are called CommandBars. The menu editor is gone, but users can edit CommandBars using the Customize dialog box (select Tools- Customize).
Is there an easy way to create a new menu on the fly?
Yes. Follow this link.
When I change a menu using the Customize dialog box, the menu is changed permanently. How can I make the menu change apply only to one workbook?
You'll need to perform you menu changes (using VBA code) when the workbook is opened, and restore the menu to normal when the workbook is closed.
I know you can use the FaceId property to add an image to a toolbar control. But how do I figure out which FaceID value goes with a particular image?
Microsoft didn't provide any way to do this, but several utilities exist that make it easy to identify the FaceID values. Follow this link to download such a utility.
I attached a new version of my toolbar to a workbook, but Excel continues to use the older version. How do I get it to use the new version of my toolbar?
When Excel opens a workbook that has an attached toolbar, it displays the toolbar only if one with the same name does not already exist on the user's system. The best solution is to write VBA code to create the toolbar on the fly when the workbook is opened, and delete it when the workbook is closed.
I've made lots of changes to Excel's toolbars. How can I restore all of these toolbars to their original state?
You can use the Customize dialog box and reset each one manually. Or, run the subroutine listed below.
Sub ResetAllToolbars() For Each tb In CommandBars If tb.Type = msoBarTypeNormal Then If tb.BuiltIn Then tb.Reset End If Next tb End Sub
How can I set things up so my custom menu is displayed only when a particular workbook is active?
You need to make use of the WorkbookActivate and WorkbookDeactivate events. In other words, write subroutines (stored in the code module for the ThisWorkbook object) that hide the custom menu when the workbook is deactivated, and unhide the custom menu when the workbook is activated.
How can I add a "spacer" between two buttons on a toolbar?
Set the BeginGroup property of the control to True.
How do you make a menu item have a checkmark next to it?
A checkmark on a menu item is controled by the State property. The statement below, for example, displays a checkmark next to
CommandBars(1).Commands("MyMenu"). _ Commands("My Item").State = msoButtonDown
To uncheck the menu item, set the State property to msoButtonUp
I accidentally deleted some items from the Worksheet menu and can't get them back. Restarting Excel doesn't fix it.
Select Tools - Customize and select the Toolbars tab in the Customize dialog box. Select the Worksheet Menu Bar item and click the Reset button.
How can I disable all of the right-click shortcut menus?
The subroutine below will do the job.
Sub DisableAllShortcutMenus() Dim cb As CommandBar For Each cb In CommandBars If cb.Type = msoBarTypePopup Then cb.Enabled = False Next cb End Sub
Is there a way to disable the shortcut menus that appear when the user clicks the right mouse button?
Yes, the statement below will do the job:
CommandBars("Toolbar List").Enabled = False
The statement listed in the previous question doesn't work!
The original version of Excel 97 had a problem with this statement. It was corrected in the SR-1 service release for Excel 97
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks