Finding cell closest to textbox

[Mike Boynton via OfficeKB.com]

I have a calendar that has the days numbers (ie: 1,2,3,4) in a textbox that
is positioned in the upper left hand corner of the cell for the day of the
week it represents. What I am try to do is highlight the cell that belongs
to the current day. I am using the following code to match the textbox to
the current day, but I can't figure out how to match the textbox to the
relative cell.
code follows...
Sub Test
On Error Resume Next
Dim MyDate As Date
Dim MyDay As Integer
Dim MySh As Integer

MyDate = Date
MyDay = Mid(MyDate, 3, 1)

For i = 1 To ActiveSheet.Shapes.Count
With ActiveSheet
ShNm = .Shapes(i).Name
MySh = Mid(ShNm, 10)
If MyDay = MySh Then
'MsgBox "The number is " & MySh
ActiveSheet.Range("G5").Interior.ColorIndex = 20
End If
End With
Next i
End Sub

 

[Jim Rech]

..Shapes(i).Topleftcell.Interior.ColorIndex = 20

--
Jim
|I have a calendar that has the days numbers (ie: 1,2,3,4) in a textbox that
| is positioned in the upper left hand corner of the cell for the day of the
| week it represents. What I am try to do is highlight the cell that
belongs
| to the current day. I am using the following code to match the textbox to
| the current day, but I can't figure out how to match the textbox to the
| relative cell.
| code follows...
| Sub Test
| On Error Resume Next
| Dim MyDate As Date
| Dim MyDay As Integer
| Dim MySh As Integer
|
| MyDate = Date
| MyDay = Mid(MyDate, 3, 1)
|
| For i = 1 To ActiveSheet.Shapes.Count
| With ActiveSheet
| ShNm = .Shapes(i).Name
| MySh = Mid(ShNm, 10)
| If MyDay = MySh Then
| 'MsgBox "The number is " & MySh
| ActiveSheet.Range("G5").Interior.ColorIndex = 20
| End If
| End With
| Next i
| End Sub
|
| --
| Message posted via OfficeKB.com
| http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200601/1

 

[Mike Boynton via OfficeKB.com]

.Shapes(i).Topleftcell.Interior.ColorIndex = 20

|I have a calendar that has the days numbers (ie: 1,2,3,4) in a textbox that
| is positioned in the upper left hand corner of the cell for the day of the
[quoted text clipped - 23 lines]
| Next i
| End Sub

Thanks for the help Jim, that does the trick. I just relised while I was
doing some more testing that the .Name property is returning the wrong
textbox. What I need to look at is the text that is in the textbox, but when
I change my code from ShNm = .Shapes(i).Name to ShNm = .Shapes(i).Text it
does not find anything. Am I using the wrong property?