Cell Reference lookup Issue

[BrandonWLH]

I'm sorry I can't provide any code or a XLS sheet for this due to an NDA, but I will explain as fully as I can because I have hit a brick wall.

As part of a change logging system within a workbook, I have it tracking any cell changes and populating a logsheet, I have it populating it with various values as well as the Sheet and Address of the cell change. Now, what I can't get it to do properly is put the name of the Cell Range that the cell may belong to as an added measure of auditing.

All the tables in the spread sheet are dynamic and could have from 10 entries to 10k, so individual names for each cell would not be an option. Name ranges grow and shink with the way I add and remove rows from tables, so I need to stick with that.

My plan was to have certain critical ranges be given a Name Reference, and then get the name of the Referenced area that the individual cell that was changed was located in, and I cannot get this to work properly.

Single cells were a snap, Target.name.name, but as soon as it was a range of cells, I had to use another option.

I tried using code similar to this

Function RangeToNameString(inputRange As Range) As String    
Dim oneName As Name        
RangeToNameString = "no name"        
  For Each oneName In ActiveWorkbook.Names        
   With oneName            
      If TypeName(Evaluate(.RefersTo)) = "Range" Then                
       If Not (Application.Intersect(inputRange, .RefersToRange) Is Nothing) Then                   
         RangeToNameString = .Name                   
        ' Exit Function: Rem optional               
        End If            
      End If       
    End With    
   Next one
NameEnd Function
</PRE>

But always would run into issues since the workbook is 10 sheets with over 100 reference names and there are overlap issues and the proper range is never given.

I need a way I can get the Named Range that a cell may be within, be it a named single cell range or a Named group of cells and not have it overlap. It is possible for me to do this if I break my logging module into 10 parts and replicate the code 10 times and tweak it per sheet, but that is some spaghetti programming and I would really like to avoid that.

Parameters
The user is ON the active sheet of many possible sheets.
Makes a change in a range area named TestRange, but there are 50 others names availible.
Target range is passed to the logging system with the cell range that was changed.

 

[BrandonWLH]

Hate to bump, but I need some ideas.

Is there some way I can get it to only look at reference names on a per sheet basis? The names seem to be parented to the Workbook and not the sheet so I can't do it that route.

The overlaps aren't on a sheet basis, but the same area on one sheet is names different than the same area on another sheet and it grabs the 1st **** area it hits that is part of the intersection.

 

[BrandonWLH]

You close this, I got a solution I came up with.

I name references on a Sheet Scope basis (SheetName!ReferenceName) and not global basis (ReferenceName) and then I can loop through the names on a particular sheet only and get the reference that way w/o multi sheet overlaps. I just send the active sheet into the logging modules as a range parameter and done.