Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Lookup to Find Closest Date and Next Closest Date | Excel Worksheet Functions | |||
finding a date/time in a list that is closest to an existing date/ | Excel Discussion (Misc queries) | |||
Finding the closest number in column A and take the value in column B | Excel Worksheet Functions | |||
Finding Closest Match | Excel Worksheet Functions | |||
Finding closest value within an array | Excel Programming |