Finding cell closest to textbox
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.a...mming/200601/1 |
Finding cell closest to textbox
..Shapes(i).Topleftcell.Interior.ColorIndex = 20
-- Jim "Mike Boynton via OfficeKB.com" <u10467@uwe wrote in message news:59f6fb557693d@uwe... |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.a...mming/200601/1 |
Finding cell closest to textbox
Jim Rech wrote:
.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? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200601/1 |
All times are GMT +1. The time now is 01:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com