Macro based on Focus

Posted by Jason Hendry on October 12, 2001 9:20 AM

Is it possible to write a macro so that when the focus (cursor location) is moved to a particular cell, a value changes in another cell?

My sheet calculates workdays to completion for a number of tasks. I can display a grid of tasks and their timeframes. It would be of great help if I could make it so that the individual selects the type of cell noting the type of work being done and the time info for that type of work would automatically appear in the cell used for the formula.

Posted by Jonathan on October 12, 2001 12:06 PM

I think this should give you some ideas. I think there are other, maybe simpler ways, but I'm not conversant with them. This seems to need absolute cell addressing, thus the $ signs.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Application.Volatile

Select Case ActiveCell.Address

Case "$D$1"
MsgBox "Do the 1st thing."
Case "$D$2"
MsgBox "Do the 2nd thing"
Case "$D$3"
MsgBox "Do the 3rd thing"
Case Else
MsgBox "Do the right thing!"

End Select

End Sub