copy

Friday, 6 December 2013



Workbook Index Add-In


Introduction



As the name implies, the Workbook Index Add-In is an Excel COM add-in that was designed to automate the procedure of creating an index of all the sheets (both worksheets and chart sheets) that are included in the active workbook. The Workbook Index Add-In was developed in Microsoft Visual Basic 6 and can be used with any Excel version that has the new Ribbon design (i.e. versions 2007, 2010 and 2013).



Installation



The installation process is quite easy and straightforward. It is suggested to close any Excel instance that might be open before you proceed:

Step 1: Unzip the Workbook Index Add-In zip file.

Step 2: In the folder that will be created double click the Setup.exe file.

Step 3: Press the OK button to proceed the installation.

Installation 1

Step 4: In the next window, press the button that looks like computer in order to install the add-in.

Installation 2

If you need to change the default directory – which is usually the Program Files folder – use the Change Directory button, specify the path of the folder you wish to install the add-in and then press the OK button. Then, press the button that looks like computer in order to install the add-in to the specified folder.

Installation 3

Step 5: Finally, a message box will inform you about the installation process.

Installation 4

After finishing the installation open Excel. The add-in button “Create Index” will appear at the Home tab of the Ribbon (at the end of the tab).

Workbook Index Add-In - Ribbon Button



How it works



By pressing the “Create Index” button the add-in will create a complete list of all the sheets that are included in the active workbook. The list is in reality a list of hyperlinks, so by clicking on the sheet’s name you will be transferred to the corresponding sheet. The add-in creates a new worksheet in the active workbook (named Index), loops through all the sheets in order to get their names and writes their names at the Index sheet. Then, it creates hyperlinks to the correspond sheets. The process is quite fast, so even if the workbook contains for example 225 sheets, the index can be created in 2 – 3 seconds.

One interesting feature of Workbook Index Add-In is the ability to hyperlink to chart sheets. Unlike worksheets, chart sheets cannot be hyperlinked since they don’t have a range property. If the add-in detects that there is at least one chart sheet at the workbook, automatically it will create a SelectionChange event at the Index sheet. This event actually reproduces the hyperlink behavior, so if the user clicks the chart sheet’s name, the VBA code of the event will activate the corresponding chart sheet. The only drawback of this method is that the workbook should be saved as macro-enabled type (XLSM, XLSB or even the old XLS) if previously has no macros. The add-in contains a lot of code for error handling, so in any case you will be informed about the success, or the reason of the failure – if something goes wrong (i.e. protected workbook structure).



Uninstallation



At any time you can remove the add-in from your computer following the typical procedure of removing a software from Windows:

Step 1: Close any instance of Excel that you might have open.

Step 2: Go to Start -> Control Panel and double click to Programs and Features icon.

Step 3: Find the Workbook Index Add-In in the list and press the Uninstall/Change button.

Uninstallation 1

Step 4: In the application removal window press the Yes button.

Uninstallation 2

Step 5: A message box will inform you about the uninstallation process.

Uninstallation 3



Demonstration video



The video below demonstrates the installation, usage and uninstallation of Workbook Index Add-In to a computer with Excel 2013. Five different case studies are demonstrated:
  1. XLSX file containing only worksheets (225 worksheets in total).
  2.  XLSX file with some chart sheets: the message box at the end prompts the user to save the file as macro-enabled type due to the addition of SelectionChange event at the Index worksheet.
  3. XLS file with some chart sheets: unlike the previous case, the XLS file format can contain VBA code, so no prompt for saving.
  4.  XLSM file with password-protected workbook structure: the message box prompts the user to unprotect the workbook structure and retry. After the protection removal, the index is created normally.
  5.  XLSM with some chart sheets: similar to XLS case, no prompt for saving.



Downloads



Download

In order to use Workbook Index Add-In you must have at least Excel 2007 or newer (2010, 2013) installed at your computer. The add-in has been successfully tested in both Excel 2007 (32 bit) and Excel 2013 (32bit). The operating system was Windows Server 2008 and Windows 7 Professional (64bit) correspondingly

Did you like this post? If yes, then share it with your friends. Thank you!


Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info