Consulting

Results 1 to 8 of 8

Thread: Solved: "Tagging" a Cell

  1. #1

    Solved: "Tagging" a Cell

    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!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,140
    Location
    [vba]

    Range("A1").Name = "cellA1"
    Activeworkbook.Names("cellA1").Visible=False
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    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!

  4. #4
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Why not run through all the names rather than all the cells?

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,140
    Location
    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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    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!

  7. #7
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    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

  8. #8
    Awesome, thanks for all of the help guys.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •