Keep Track of Names in Excel Workbook

You probably use defined names in some of your Excel workbooks. We’ll look at a built-in way to list the names in a workbook, and see some Excel VBA code that creates a more detailed list of names.

Using Names in Excel

You can name a group of cells, and use that name as the source for a data validation drop down list. For example, if you have entered a list of the products that you sell, you could select the list, and name the range reference as ProdList. Then, that product list could be used in an order form.
NamesList00
If you created Excel Tables, in Excel 2007 or Excel 2010, they are automatically named, and you can change the names to something meaningful, such as ProdTable.
NamesList00a

Create a List of Names

If you’re working on a complex Excel workbook, it’s easy to lose track of what you’ve named, and where the named ranges are located. For reference, you can print out a list of names, using a built-in feature in Excel.
To paste a list of workbook level names in Excel:

  • Insert a blank worksheet
  • On the Excel Ribbon, click the Formulas tab
  • In the Defined Names group, click Use in Formula, and click Paste Names (the keyboard shortcut is F3)

NamesList01

  • In the Paste Name window, click Paste List.

NamesList02
A list of defined names and their formulas is pasted into the worksheet.
NamesList03
Note: To see worksheet level names, use the Paste List feature on the worksheet where those names are defined.

Create a Names List with Excel VBA

The built-in names list feature is helpful, but if you need more details, you can create your own list, by using Excel VBA. In this example, the code lists all the named and unlocked cells on an order form sheet.
NamesList05
The list includes the cell address, cell value, cell name (if applicable), row number, column number, and “Yes”, if the cell is locked.
With this list, you can see that there is a problem in cell B5 — the cell is named, and users should be able to select a customer name from a drop down list. However, the YES in column F shows that the cell is locked, so when the worksheet is protected, no one will be able to select a customer in the order form.
NamesList04

The Names List Code

Here is the code that creates the Names List. It also lists cells that are unlocked, whether or not they are named.

Sub ListUnlockedCells()
Dim c As Range
Dim wsNew As Worksheet
Dim lRow As Long
Dim strName As String
Dim strSheet As String
On Error GoTo errHandler
Application.DisplayAlerts = False
Application.ScreenUpdating = False
strSheet = "UnlockedOrNamedCells"
lRow = 2
On Error Resume Next
  Worksheets(strSheet).Delete
On Error GoTo errHandler
Set wsNew = Worksheets.Add
wsNew.Name = strSheet
wsNew.Range("A1:F1").Value = _
    Array("Cell", "Value", _
      "Name", "Row", "Col", "Locked")
wsNew.Rows(1).Font.Bold = True
For Each c In wksOrder.UsedRange
  On Error Resume Next
  strName = c.Name.Name
  If Err.Number = 1004 Then
      strName = ""
  End If
  On Error GoTo 0
  If c.Locked = False Then
    With wsNew
      .Range(.Cells(lRow, 1), .Cells(lRow, 6)).Value _
        = Array(c.Address, c.Value, strName, _
            c.Row, c.Column, "")
    End With
    lRow = lRow + 1
  Else
    If strName <> "" Then
      With wsNew
        .Range(.Cells(lRow, 1), .Cells(lRow, 6)).Value _
        = Array(c.Address, c.Value, strName, _
            c.Row, c.Column, "YES")
      End With
    lRow = lRow + 1
    End If
  End If
Next c
MsgBox "Done"
exitHandler:
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
  Exit Sub
errHandler:
  MsgBox "Could not print Names List"
  Resume exitHandler
End Sub

Download the Names List Sample File

To see the sample workbook, and the Names List code, you can download the Excel Names List sample workbook. The file is zipped, and in Excel 2007 file format, and it contains a macro.
___________