Solved: "Tagging" a Cell

[bbk5]

So, I'm attempting to tag cells in an XLS file with an additional string that helps me identify where it came from. I need this to know what to do with the value in that "tagged" cell. My first attempt was to save this string into the ID of that cell. This approach works as long as I don't close out the XLS file. If I do, the ID's of all the fields seem to be cleared.

So now, I'm looking for another way to accomplish this. I have tried using the name field of a cell but my problem with that was I get a huge list of names in the dropdown in Excel. If there was a way to prevent the names from appearing in that list, that would be the most ideal method.

If anyone has any ideas on how to tag a cell with some additional information, I'd really like to hear them. Thanks!

[Bob Phillips]

[vba]

Range("A1").Name = "cellA1"
Activeworkbook.Names("cellA1").Visible=False
[/vba]

[bbk5]

Thanks for the tip.

Also, is there a way to discover if a specific cell has a name or not? I run through all of the cells in a worksheet and try to get their name. Problem is, some of them don't have names assigned to them and that generates an error. I don't really want to have generate and catch the error everytime a name doesn't exist for a cell. Is there another way?

Thanks!

[Norie]

Why not run through all the names rather than all the cells?

[Bob Phillips]

Here is one way

[vba]

Public Sub Test(ByVal testRng As Range)
Dim nme As Name
Dim rng As Range
For Each nme In ActiveWorkbook.Names
Set rng = Nothing
On Error Resume Next
Set rng = Range(nme)
On Error GoTo 0
If Not rng Is Nothing Then
If rng.Parent.Name = testRng.Parent.Name Then
If Not Intersect(testRng, rng) Is Nothing Then
MsgBox "Part of " & nme.Name & ", " & nme.RefersTo
End If
End If
End If
Next nme
End Sub
[/vba]

[bbk5]

Thanks for that function.

Also, I wouldn't mind running through all of the names but I'd only want to do it for a particular sheet. However, even though I assign names to a cell like this:

Sheets(sheet).Cells(row, col).name = name

when I do the following:

Sheets(sheet).names.count

I always get a value of 0. It seems that the names are only being registered under the workbook and not the sheets themselves. Any idea on how to get a Cells name to be within the Names collection of its parent sheet? Thanks!

[rory]

You can do it two ways:
1. Prefix the name you give the range with the name of the worksheet and an exclamation mark; or
2. Use the Worksheet.Names.Add method.

HTH
Rory

[bbk5]

Awesome, thanks for all of the help guys.