Name Of Range With Active Cell

RADCON · May 22nd 2010

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:

Code

Sub DefineNameandwatermark2007()


    Dim i   As Long
    Dim a, b, c, X
    Dim dt  As Date
    Dim sr As String
    dt = DateSerial(2007, 1, 1)
    
    For i = 8 To 25000 Step 66
        j = j + 1
        a = Application.WorksheetFunction.Text(dt + j, "ddd")
        b = Application.WorksheetFunction.Text(dt + j, "mmm")
        c = Application.WorksheetFunction.Text(dt + j, "d")
        X = a & "_" & b & "_" & c
        ActiveWorkbook.Names.Add Name:=X, RefersToR1C1:="=2007!R" & i & "C1:R" & _
        i + 65 & "C1"
                
         With ActiveWorkbook.Names(X).RefersToRange
           
           
           sr = Format(dt + j, "dddd, mmmm d")
           
           ' top of range
            sngTop = .Top + 250
           
       End With
        
        ActiveSheet.Shapes.AddTextEffect(PresetTextEffect:=1, _
        Text:=sr, FontName:="Arial Black", FontSize:=50, _
        FontBold:=False, FontItalic:=False, Left:=60, Top:=sngTop).Select
          
' define the text dimensions
With Selection.ShapeRange
     .ScaleHeight 1.23, False
     .ScaleWidth 1.6, False


     ' Solid or no color
     .Fill.Visible = False
     '.Fill.Solid
     '.Fill.ForeColor.SchemeColor = 22
     '.Fill.Transparency = 0.1
     
     ' Outline
     .Line.Weight = 0.2
     .Line.DashStyle = 1
     .Line.Style = 1
     .Line.Transparency = 0#
     .Line.Visible = True
     .Line.ForeColor.SchemeColor = 22
     .Line.BackColor.RGB = RGB(255, 255, 255)


     .Height = 90
     .Width = 700
End With


    Next i
End Sub

Display More

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

Code

Sub cellINBMs()
Dim nName As Name, i As Integer, str As String
str = ""
'[COLOR="Blue"]The next line is the error[/COLOR]
For Each nName In Names _
 If Not Intersect(Selection, Range(nName)) Is Nothing Then 
   str = str & nName.Name & ";"
 End If
Next nName
If Len(str) <= 1 Then
 str = ""
 Else: str = Left(str, Len(str) - 1)
End If
 MANAGERCONTROLSFORM.SELECTEDDAYLABEL.Caption = Format(str, "dddd, mmmm d")
'MsgBox str
End Sub

Display More

NOTE: I have been calling this procedure from

Code

Sub Worksheet_SelectionChange(ByVal Target As Range) 'sheet 2007
cellINBMs
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 dt=dateSerial...is 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

Name Of Range With Active Cell

Participate now!

Share