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:
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
so that the following will return the name of the range containing the active cell
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
NOTE: I have been calling this procedure from
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!