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.

___________

You may also like...

10 Responses

  1. Debra: I want to know, why don’t you host an Excel forum on your website? Not sure about your goals with this site but a forum can help you bring a lot of traffic.

  2. hanan says:

    Hi

    Can you please explain how the code works, I didn’t understand where did you defined the “wksOrder” object?

  3. Gregory says:

    I didn’t realize you could simply paste a list of names. Thanks for that tip.

  4. Wayne says:

    This doesn’t necessarily produce a list of names. It only produces a list of names that refer to a single cell. It doesn’t handle names that refer to broader ranges (e.g. A1:B2) or names that refer to a constant (=”ABC”).

    wksOrder isn’t set in the code.

    To get info about the names, wouldn’t it be better to cycle through the names and for each name report on either its constant value or its .RefersToRange? Of course, that doesn’t produce a list of unlocked cells but I’m struggling to see the value in that anyway.

    But I love the example, keem ’em coming.

  5. Glenn From Iowa says:

    Just wanted to clarify that, even though this page talks about naming Excel Tables – that you can change the names to something meaningful, such as ProdTable – Table Names are not included in the built-in tool in Excel that lists Names (even though tables are listed in the Name Box to the left of the Insert Function and Formula Bar boxes). Neither are Table Names included in The Names List Code.

    This is not to imply that the page doesn’t do everything it’s advertised to. I was just hoping to find code about listing Table Names too. I’ll keep looking.

  6. mcm says:

    I was hoping you can help me, we are trying to find a way to track Parent and the child’s name for things like early dismissal, Tardy, Visiting, or Volunteering and then at the end of the month running a report to see how many times they were late, left early, visited or Volunteered. Is there away to do this in excel??

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *