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
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