Workbook Command Bar

The page describes the Workbook CommandBar COM Add-In that creates a command bar to display the open workbooks and allows one-click access to activate the any worksheet in any open and visible workbook. This same functionality is avaiable from the Window menu, but some users have indicated the desire for a dockable solution.

When you load the COM Add-In, it will create a command bar, as shown below:

Workbook Command Bar

You can position the command bar as a floating bar within the worksheet window or you can dock the command bar at the top of the Excel window or on the left, bottom, and right sides of the window.

You can download the installation file here. Save the zip file to folder of your choice and unzip the file. Run the Setup.exe file. This will install the files required. Then, open Excel. The commmand bar should be displayed on your screen. Position the command bar in the desired location on the screen. The position setting is saved in the System Registry when you close Excel, so the command bar will automatically position itself the next time you open Excel.

The code uses Excel Events to refresh itself when a file is opened or closed or saved. However, it is possible that the buttons will get out of sync with the open workbooks in Excel. You can click the Refresh button on the command bar, shown below, to sync up the command bar with the files that are open in Excel. Note that only workbooks with a visible window are included on the command bar. Hidden workbooks are not shown.

In addition to the buttons for each worksheet, there are three control buttons that are always visible on the command bar.

Workbook Command Bar Controls

The Info button displays a basic About box.

The Navigate button displays a 'tree-view' control of all open and visible workbooks and their worksheets. You can navigate directly to a worksheet by clicking it in the tree-view window. The Navigation window is shown below:

The Refresh button synchronizes the command bar buttons with the workbooks open in Excel. The command bar can get out of sync with Excel during a Save As operation, since the add-in has no way to determine the new name of a file that was renamed with a Save As operation.

The WorkbookCommandBar COM Add-In requires the RegistryWorx DLL file, included in the setup program and described here . The setup program will automatically install and register the RegistryWorx DLL if necessary. In some circumstances (especially under Vista) you may need to re-register the RegistryWorx DLL. Doing so will solve an Error 429 - Cannot Create Object error. With Excel closed, go to the Windows Start menu, choose Run, and enter the following, with the quotes as shown:

RegSvr32 "C:\Windows\System32\RegistryWorx.ddl" /u

Then repeat this but omit the /u switch. On some systems, such as Windows 2000, you need to use WinNT in place of Windows

The COM Add-In was written in Visual Basic Version 6 as an ActiveX DLL, shared add-in. If you are interested in seeing the VB6 source code, send me an email.