Solved

Return defined name of cell

Posted on 2010-11-24
5
380 Views
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)
        Else
        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
        Else
        End If
    Next rCell
Next N

End Sub

Open in new window

0
Comment
Question by:Haydan
  • 2
  • 2
5 Comments
 
LVL 23

Expert Comment

by:Michael74
ID: 34209582
0
 
LVL 23

Expert Comment

by:Michael74
ID: 34209610
0
 
LVL 81

Accepted Solution

by:
byundt earned 250 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
0
 

Author Closing Comment

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

Expert Comment

by:byundt
ID: 34209847
Haydan,
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.

Brad
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

839 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