Hide and unhide objects in Excel
03.09.2009
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. 
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.
Public Function mlfpMainShapes(Book As String, _
Sheet As String)
Dim c As Long
Dim n As Long
' Bypass...
On Error Resume Next
' Check...
If Not mlvpHandle Is Nothing Then
' With...
With Application.Workbooks(Book).Worksheets(Sheet)
' Bypass...
mlvpBypass = True
' Clear...
mlvpHandle.Controls("LSV_0001").ListItems.Clear
' Reset...
mlvpHandle.Controls("CHK_0001").Value = False
mlvpHandle.Controls("CHK_0002").Value = False
mlvpHandle.Controls("EDT_0001").Text = ""
mlvpHandle.Controls("EDT_0002").Text = ""
' States...
mlvpHandle.Controls("CHK_0001").Enabled = False
mlvpHandle.Controls("CHK_0002").Enabled = False
' Settings...
mlvpHandle.Controls("LSV_0001").Gridlines = False
mlvpHandle.Controls("LSV_0001").HideSelection = True
mlvpHandle.Controls("LSV_0001").View = lvwList
' Events...
DoEvents
' Bypass...
mlvpBypass = False
' Check...
If .Shapes.Count > 0 Then
' Bypass...
mlvpBypass = True
' Settings...
mlvpHandle.Controls("LSV_0001").Gridlines = True
mlvpHandle.Controls("LSV_0001").HideSelection = False
mlvpHandle.Controls("LSV_0001").View = lvwReport
' Add...
For n = 1 To .Shapes.Count
' Current...
c = mlvpHandle.Controls("LSV_0001"). _
ListItems.Count + 1
' Add...
mlvpHandle.Controls("LSV_0001"). _
ListItems.Add , , CStr(c)
' Events...
DoEvents
' Data...
mlvpHandle.Controls("LSV_0001").ListItems(c). _
SubItems(1) = .Shapes(n).Name
' Check...
If .Shapes(n).Visible Then
mlvpHandle.Controls("LSV_0001"). _
ListItems(c).SubItems(2) = "•"
End If
Next n
' Selection...
mlvpHandle.Controls("LSV_0001"). _
ListItems(1).SelectedItem = True
mlvpHandle.Controls("LSV_0001"). _
ListItems(c).EnsureVisible
' States...
mlvpHandle.Controls("CHK_0001").Enabled = _
Not .ProtectDrawingObjects
mlvpHandle.Controls("CHK_0002").Enabled = _
Not .ProtectDrawingObjects
' Events...
DoEvents
' Bypass...
mlvpBypass = False
' Update...
mlfpMainShapesItem
End If
' Visibility...
mlvpHandle.Controls("BTN_0007").Visible = True
mlvpHandle.Controls("BTN_0008").Visible = _
CBool(.Shapes.Count > 0)
mlvpHandle.Controls("BTN_0009").Visible = _
CBool(.Shapes.Count > 0)
End With
End If
End Function
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.
' Application...
Public WithEvents App As Application
'
' Events
'
' ..............................................................
Private Sub App_SheetActivate(ByVal Sh As Object)
mlfpMainShapes Sh.Parent.Name, Sh.Name
End Sub
Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
mlfpMainShapes Wb.Name, Wb.ActiveSheet.Name
End Sub
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.
' Userform...
Private Sub UserForm_Initialize()
' Bypass...
On Error Resume Next
' Api...
mlfpApiMenueRemove Me.Caption, True, True
' Create...
Set mlvhInstance = New MLC_Application
' Handles...
Set mlvpHandle = Me
Set mlvhInstance.App = Application
' Fill...
LSV_0001.ListItems.Clear
' Columns...
LSV_0001.ColumnHeaders.Add , "Number", "Number", 0
LSV_0001.ColumnHeaders.Add , "Name", "Name"
LSV_0001.ColumnHeaders.Add , "State", "State", 16
' Widths...
LSV_0001.ColumnHeaders(2).Width = _
LSV_0001.Width - 16 - _
LSV_0001.ColumnHeaders(3).Width - _
LSV_0001.ColumnHeaders(1).Width
' Settings...
LSV_0001.AllowColumnReorder = False
LSV_0001.FullRowSelect = True
LSV_0001.Gridlines = False
LSV_0001.HideColumnHeaders = True
LSV_0001.HideSelection = True
LSV_0001.LabelEdit = lvwManual
LSV_0001.View = lvwList
' States...
CHK_0001.Enabled = False
CHK_0002.Enabled = False
EDT_0001.Enabled = False
EDT_0002.Enabled = False
' Visibility...
BTN_0007.Visible = False
BTN_0008.Visible = False
BTN_0009.Visible = False
End Sub
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.
Blog
News
