Name Of Range With Active Cell

  • I have been trying to make a label tell the user which range their selected cell happens to be in. The code to return a selected named range is correct, it just dosen't work for my circumstance ie. the technique I used to name the ranges. Can someone please help me modify the following:

    so that the following will return the name of the range containing the active cell

    NOTE: I have been calling this procedure from

    Sub Worksheet_SelectionChange(ByVal Target As Range) 'sheet 2007
    End Sub

    More info:
    1. If I used the preceding chunk of code on a new work book where I had named A1:B29 "first range" and use MsgBox str to display the named range when I click any cell in the range. it works!
    2. I named ranges in 4 sheets (2006, 2007, 2008, 2009) and as each range is named for a consecutive day, col A holds (7:00,7:15,(am)....till 11:00 (pm)). which is why each range is exactly 65 rows. Would nameing all ranges for all sheets in one big proceure make things work?
    3. What about the potential for 2 ranges named for the same date on different sheets? how can I modify my code to indicate the year in the range name also
    4. does my problem have to do something with there a format issue inside the range names? (I have almost ruled this out)
    5. Did the way I named my ranges use the first cell as a refrence cell or something, so that other cells are not in the range? I have been worried about this one the most, and have tried a million different things over the last week to figure it all out.

    Any new theories?...My application rules if I can get this to work!
    Thank You

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!