display names of defined cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to visually show on the spreadsheet which cells have been
named and what the names are? Something like a coloured border around the
cells and the name displayed like a comment box? I don't actually need it in
this particular format but would just like to see where my named cells are so
I can edit them if I see errors.
 
Is there a way to visually show on the spreadsheet which cells have been
named and what the names are? Something like a coloured border around the
cells and the name displayed like a comment box? I don't actually need it
in
this particular format but would just like to see where my named cells are
so
I can edit them if I see errors.
Click to expand...

In the "names" field on the left of the formula bar, there is a drop-down
arrow... click it and select the range name from the list which will
highlight the cells it is composed of.

Rick
 
Easiest way to get a overview of your named cells is to just change the zoom
view %.

Just adjust the zoom to any number less than 40%, excel will display all
names highlighted in Blue font with a border.
 
Easiest way to get a overview of your named cells is to just
change the zoom view %.

Just adjust the zoom to any number less than 40%, excel will
display all names highlighted in Blue font with a border.
Click to expand...

That doesn't appear to be the case if the named range is not contiguous.

Rick
 
Hi,

Note: To the left of the formual bar, there is "Name Box" text box. When you
create a new file (Book), the current cell location is displayed in the Name
Box. However, this "Name Box" becomes a list, only if you have defined one or
more named definitions.

To know which reference each named definition is associated, select an item
from the Named Box list. The reference for the selected named definition is
auotmatically shown in the workbook.

Challa Prabhu
 
Here's a quick one for you. Enable macros, paste this somewhere
(personal macros workbook if you have one), activate the workbook for
which you want names, and hit Play. It will create a new worksheet in
that workbook called "Named Ranges" (and will delete any existing ones
by this name, so change that string if you have one), and list all the
ranged addresses and formulas in them.



Public Sub listNames()
' any worksheet by this name will be deleted and replaced
Const worksheetName As String = "Named Ranges"

Dim myName As Excel.Name
Dim wsh As Excel.Worksheet
Dim blnSetting As Boolean
Dim recordRow As Long

Application.ScreenUpdating = False

On Error Resume Next
Set wsh = ActiveWorkbook.Worksheets(worksheetName)
On Error GoTo 0

If Not wsh Is Nothing Then
blnSetting = Application.DisplayAlerts
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets(worksheetName).Delete
Application.DisplayAlerts = blnSetting
End If

Set wsh = ActiveWorkbook.Worksheets.Add
recordRow = 2

With wsh
.Name = "Named Ranges"

If ActiveWorkbook.Names.Count Then
.Cells(1, 1).Value = "Name"
.Cells(1, 2).Value = "Refers To"
For Each myName In ActiveWorkbook.Names
.Cells(recordRow, 1).Value = myName.Name
.Cells(recordRow, 2).Value = "'" & myName.RefersTo
recordRow = recordRow + 1
Next myName
Else
.Cells(1, 1).Value = "No names defined in active
workbook."
End If
With .Range("A1:C1")
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With
End With

Application.ScreenUpdating = True
End Sub



Hope this helps.
 
Sorry.... change this:

With wsh
.Name = "Named Ranges"


To this:

With wsh
.Name = worksheetName


Last-minute changes never go smoothly!
 
ooh! that's too dificult for me! Can you walk me through this step-by-step?
I've never written a macro before.
:o)
 
yeah, I know about that drop-down list but I'm actually looking for a display
of all named cells at once...
Somebody posted a macro, I may try that out...
Any other ideas?
 
Back
Top