(C) 2009 - 2014 by Mourad Louha · All rights reserved

Hide and unhide objects in Excel

I found an interesting article on the Microsoft Excel Blog from Ben Rampson (Program Manager on the Excel Team) about hiding and unhiding objects in Excel. 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.

Objects in Excel

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.

Objects in Excel

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.

Objects in Excel

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:

 

 
Comments

That is great news but do you know how to use the hide object via the selection pane with a macro. I cannot get a macro to run and hide the objects on the sheet using MC Excel 2007 and Vista OS.

Mourad Louha

Hi Michael…

as I know, it is not possible to access the selection pane directly by using VBA macros. You may, for example, iterate the objects by yourself.

Regards

Leave a Reply