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.
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.
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)
- In the Paste Name window, click Paste List.
A list of defined names and their formulas is pasted into the worksheet.
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.
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.
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.