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

Share