Finding cell closest to textbox

  • Thread starter Mike Boynton via OfficeKB.com
  • Start date
M

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
 
J

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
 
M

Mike Boynton via OfficeKB.com

Jim said:
.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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top