Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Return defined name of cell

Posted on 2010-11-24
Medium Priority
Last Modified: 2012-05-10
I'm creating a verification code for a workbook and want to autopopulate cells based on the colour of the cell.

Some cells need to be filled in with that cells defined name so that they can be easily tracked throughout the workbook. I've attached the code that I am trying to do this with (stuck getting the second loop to return cells defined name) but it is currently just returing the cell's location on the sheet not the defined name of that cell.
Private Sub CommandButton5_Click()

    Dim rCell As Range
    Dim pg As Worksheet
    Dim N As Single
    Dim aColor As Long ' Data entry for random number (Light Blue)
    Dim bColor As Long ' Data entry for words - set by cell defined name (Very Pale Blue)
    Dim cColor As Long ' Verification data that shouldn't be changed but should be removed prior to issue (Tan)
    Dim dColor As Long ' Data/Formulas that shouldn't be changed or removed prior to issue (Very Pale Green)
    Randomize ' Initiate random number generator
    aColor = RGB(153, 204, 255) 'Specify the color range for random numbers
    bColor = RGB(204, 255, 255)
    cColor = RGB(255, 204, 153)
    dColor = RGB(204, 255, 204)

For N = 1 To Sheets.Count
    For Each rCell In Sheets(N).Range("A1:AZ500")
        If rCell.Interior.Color = aColor Then
        rCell.Value = 0.6 + (0.3 * Rnd)
        End If
    Next rCell
Next N

For N = 1 To Sheets.Count
    For Each rCell In Sheets(N).Range("A1:AZ500")
        If rCell.Interior.Color = bColor Then
        rCell.Value = rCell.Name
        End If
    Next rCell
Next N

End Sub

Open in new window

Question by:Haydan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 23

Expert Comment

by:Michael Fowler
ID: 34209582
LVL 23

Expert Comment

by:Michael Fowler
ID: 34209610
LVL 81

Accepted Solution

byundt earned 1000 total points
ID: 34209648
If the workbook has been saved and the name refers to a single cell, then you can use:
rCell.Value = rCell.Name.Name

Author Closing Comment

ID: 34209721
Why do you have to go .Name.Name?
LVL 81

Expert Comment

ID: 34209847
The explanation is a little tricky:
The Name property of a Range object returns a Name object
The default property of a Name object is its address
The Name property of a Name object is the name of that object

So following the first and second points, rCell.Name will return something like =Sheet1!A1
Following all three points, rCell.Name.Name returns what you need--the name of the cell

I had to look up the syntax of the Name property and object to arrive at the explanation. But I've encountered this question before, and it was far easier for me to remember the stuttering pattern of .Name.Name as the way to get the answer.


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question