I found an interesting article on thefrom Ben Rampson (Program Manager on the Excel Team) about . So, it’s very easy to do this in Excel 2007 as you can select from the Start Tab the entry “Selection pane” from the “Find & Select” button.
The panes shows you all objects in the current sheet, you can hide and unhide them by clicking on the eye buttons on the left side of the pane.
Unfortunately such a pane is not available in Excel 2003. Never mind, we can implement a similar pane with VBA. We’ll present here a small Add-In listing all objects in a sheet and allowing to hide and unhide them.
You can click on the red and green eye buttons for hiding and unhiding all object in the current sheet. If you like to update the list of objects, click on the button with the circular arrows. A double click selects the object in the sheet and, if it was hidden, unhides it. On the lower side of the window, you’ll get some information for the selected object in the list.
You can download the Add-In from our site at the end of this article or from our download area. If you like to have a look to the code, the password for the VBA Project is “maninweb”; without the quotes.
The core of the application is the Listview in a UserForm. The Listview is filled from the public function mlfpMainShapes() in a module.
This function expects two arguments, first, the name of the active workbook and second, the name of the active sheet. In addition the function uses the global variable mlvpHandle to acces to the loaded Userform. The loop searches all objects in the active sheet and populates the Listview. We also check if the current sheet is protected. Finally a call is made to the function mlfpMainShapesItem() for setting up the controls in the Userform.
How can we recognize that another workbook or worksheet was activated? Well, for doing this we need a class referencing to the Excel application and checking the raised events.
We only needs to intercept those events above. Our class is created and initialized in the code of the Userform, as you can see below.
As mentioned before, a button allows you to refresh the Listview. Unfortunately there is no event available in VBA which permits to recognize that the user added or deleted some shapes. In this case, the listview has to be refreshed.
You can download the Add-In here Hide and unhide objects in Excel and you are permitted to change the code or use it in your own projects. In case you extend the Add-In, we would be pleased if you inform us about your changes or improvements. However, please note that the grafics used in the Add-In are not free and should not be used for other purposes. We tested the Add-In in Excel 2003 German on Windows XP German and Windows Server 2008 German. There is no warranty nor support for this Add-In.
This article has also been published in German and in English on the following sites:
- Excel Ticker Blog, German
- Professional Excel Solutions by Maninweb.de, English
- Professional Excel Solutions by Maninweb.de, German