Hide or Show Names in Excel with VBA

by Gregory on November 6, 2010

Excel Name List Box

If you’ve discovered how useful Names can be in a spreadsheet, you may also know they can get-in-the-way-if-you-have-too-many. Just check out the Name Box in the picture to the right.

Each Name has a Visible property that’s set to TRUE by default. You can hide a Name by changing the Visible property to FALSE, but only in the VBA Editor.

Hide Names in Excel

If you have a great many names to hide, it’s best to loop through the Names collection and change the Visible property value to TRUE for all Names.

<big>Sub HideAllNames()
Dim n As Name
For Each n In ThisWorkbook.Names
If n.Visible Then n.Visible = False
Next n
End Sub</big>

After running this code, you now have a blank Name Box.
Excel Name List Blank

If you just want to hide a few Names, the code below, executed in the Immediate Window of the VBA Editor, will do the trick. Just change out “MachName” for each Name you want to hide.

Hide Name with VBA Code in Immediate Window

Go To Hidden Names

You can still Go To a Name that is hidden, just type a Name in the Name Box and hit enter.

Show Hidden Name with Name Box

Or use the keyboard shortcut Ctrl+G or F5 to bring up the Go To dialog box, type a Name in the Reference text box, and click OK.

Show Hidden Name with GoTo Dialog Box

If the Name’s corresponding range address is on a hidden worksheet, this won’t work.

Show Names in Excel

While working on a file, it can sometimes be advantageous to actually SEE ALL the Names. The following VBA code will do just that.

<big>Sub ShowAllNames()
Dim n As Name
For Each n In ThisWorkbook.Names
If n.Visible = False Then n.Visible = True
Next n
End Sub</big>

Keep Some Names Visible with Code

Sometimes you may want to leave a few of the Names visible for users. In the code below I’m looping through the Names collection, setting the Visible property to False, then checking to see if any have “Lookup” in the Name property string and making those Visible.

<big>Sub ShowSomeNames()
Dim n As Name
For Each n In ThisWorkbook.Names
n.Visible = False
If InStr(1, n.Name, "Lookup", vbTextCompare) &gt; 0 Then
n.Visible = True
End If
Next n
End Sub</big>

I use the VBA Function InStr, which stands for In String, to check and see if the Name Property of each Name has “Lookup” contained in it, by starting at character 1. If the InStr Function finds “Lookup” with in the text, it returns a numerical value, which represents the character position of where “Lookup” starts within the text string. If the InStr function doesn’t find “Lookup” within the Name Property, it returns a zero (0).

In the code above, if the InStr function finds “Lookup” within the Name Property string, the code sets the Visible property to TRUE. Here’s what shows in the Name Box after running this code.

Excel Name List INSTR Lookup

You can open up the Excel VBA Editor, create a new module, copy any of the first two code snippets, paste into the module, and it should run just fine. The third snippet is more specialized and would need modification of the “Lookup” attribute for it to work.

My apologies for not indenting the code above, but I’m still learning HTML and CSS.

Related Posts Plugin for WordPress, Blogger...

Comments on this entry are closed.

Previous post:

Next post: