ShapeNav

Like other products of the Office Suite, Excel supports drawings and charts in its worksheets. These charts are managed with objects of type "Shapes" and can also contain text data. Unfortunately, the Find feature of Excel (short-cut = Ctrl+F) is not browsing the Shapes and therefore, ignore all character strings that are embedded in drawings. For example, JCL-Drawer generates its flowcharts in Excel and writes the DSN (dataset names) in the icons of the files. The Excel Find function cannot be used to look for the other usages of a target file.

ShapeNav has been made to fill this gap.

ShapeNav is a widget that comes out as a small window of fixed size with 2 display modes :

Entry mode : The windows is displayed foreground. The user can enter the text to find and click on the equal sign or the include sign to start the research of the shapes that contain exactly the text or just containing it. The research is case sensitive. If the target text is not found in the current workbook, ShapeNav stays in entry mode et displays an alert "Not Found".

Browse mode : When ShapeNav goes to the browse mode, it is displayed in half-transparency. It browses the whole workbook (that can take a few seconds) and targets the first found shape, navigation arrows are showing the directions in which the text can be found elsewhere :

• Up / Down : On the same worksheet,
• Left / Right : On the previous or the next worksheet.

The bars at the end of the arrows are leading directly to the first / last occurence of the string on the worksheet or on the workbook. Double-click on the center of the window to switch to the entry mode.

In both modes, click on the cross sign to leave ShapeNav.

ShapeNav is a VBA macro and there are several ways to use it in an Excel workbook :

The more obvious, but not very convenient : Execute the ShapeNav macro.
Open both the workbook containing ShapeNav and the one to browse for strings. From the latter, open the window to choose the macro to execute (Alt+PF8 short-cut), select ShapeNav and click on Execute. ShapeNav is opened in the entry mode.

A bit more sophisticated, but even less convenient : Associate the ShapeNav macro to the shape containing the text you want to look for (elsewhere...).
Once again open both workbooks -ShapeNav and searched. Right-click on the shape with the searched text then click on "Assign a macro" in the contextual menu. Select ShapeNav and click on OK.
You can now open ShapeNav straight in the browse mode by clicking on the shape. The opening is longer because ShapeNav is scanning the whole workbook before displaying.

• Generalization of the previous option : Associate the ShapeNav macro to ALL the shapes containing a text.
  Get it done by a program, of course. It is a feature of JCL-Drawer which can, while it is drawing the flowcharts, associate ShapeNav to every file icon : All DSN become clickable.
• A profitable investment : Declare ShapeNav as an Excel add-in.
  The procedure underneath can vary along the different Excel versions :
  1. Save ShapeNav as an .xlam file,
  2. Click on the Office button, then "Excel Options"
  3. Click on the tab "Add-In" to the left
  4. Bottom, in the "Manage" dropdown list, select "Excel Add-in" and click on Go.
  5. Click on "Browse...", select the "ShapeNav Vn.nn.xlma" file and validate.
  6. Make sure that the checkbox before ShapeNav is ticked and click on OK.
  
  Then, add ShapeNav to your quick acces toolbar :
  1. Click again on the Office button, and "Exel Options"
  2. Click on Customize
  3. Choose the "Macros" category in the dropdown list
  4. Select ShapeNav in the list, click the Add button and validate. A new button is displayed in the quick access toolbar.
  

Currently, you cannot.

As a matter of fact, I am not pleased with the outcome : Ergonomy is poor, the window size does not adapt to the target shape, the response time is a bit too long (May be better since Excel 2010, as Microsoft has rebuilt the shape management). At last, it remains a mysterious bug that prevent the window from displaying, and we cannot click on the cross sign to quit, and Excel stays frozen there, and that is evil.

But you can nevertheless contact me if you need it -I will see what i can do to finally get rid of those problems.