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.

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

Get hold of Jan Karel Pieterse's NameManager utility
http://www.jkp-ads.com/Download.asp, it will help manage names easily.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Rick Rothstein (MVP - VB)" wrote in
message ...
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.

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.
--

Regards

Sri


"Melissa" wrote:

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.

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.

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

"Melissa" wrote:

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.

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.


On Aug 13, 2:58 am, Melissa wrote:
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.

Sorry.... change this:

With wsh
.Name = "Named Ranges"


To this:

With wsh
.Name = worksheetName


Last-minute changes never go smoothly!



On Aug 13, 3:58 pm, iliace wrote:
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.

On Aug 13, 2:58 am, Melissa wrote:



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. - Hide quoted text -

- Show quoted text -

ooh! that's too dificult for me! Can you walk me through this step-by-step?
I've never written a macro before.
:o)

"iliace" wrote:

Sorry.... change this:

With wsh
.Name = "Named Ranges"


To this:

With wsh
.Name = worksheetName


Last-minute changes never go smoothly!



On Aug 13, 3:58 pm, iliace wrote:
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.

On Aug 13, 2:58 am, Melissa wrote:



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. - Hide quoted text -

- Show quoted text -

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?

"Rick Rothstein (MVP - VB)" wrote:

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.

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

ooh! interesting! Will explore it further! Thanks for the reference.

"Bob Phillips" wrote:

Get hold of Jan Karel Pieterse's NameManager utility
http://www.jkp-ads.com/Download.asp, it will help manage names easily.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Rick Rothstein (MVP - VB)" wrote in
message ...
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.

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