MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Text message by selecting cell


Posted by Nu Flavor on December 24, 2001 4:01 AM

How can I let a text message appear in the right corner, or a comment for a cell when I select it? Not by selecting it by mouse but just moving with the arrow keys on the keyboard? How can I do this? In VB please! Thanks!

Nu Flavor


Posted by Gary Bailey on December 24, 2001 4:26 AM

Try the following in the code module of the worksheet in question. It should show the comments when the cell in question is selected. The code below only works when individual cells are selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Static rngOldSelection
On Error Resume Next

rngOldSelection.Comment.Visible = False

If Target.Comment Then
Target.Comment.Visible = True
End If

Set rngOldSelection = Target

End Sub

Gary

Posted by Nu Flavor on December 24, 2001 4:32 AM

Doesn't Work!!!

I want it for the cells F10 til F21 so if cell F10 is selected a comment for cell F10 and if cell F11 is selected a comment for cell F11 and so on... Hope you can help me?

Nu Flavor

Posted by Gary Bailey on December 24, 2001 4:50 AM

Re: Doesn't Work!!!

The following should show the comments but won't hide them when deselected. You need to put it in the code module behind the sheet you're interested in.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

On Error Resume Next
Dim rngIntersect As Range, rngCell As Range

Set rngIntersect = Application.Intersect(Target, Range("f10:f21"))

For Each rngCell In rngIntersect.Cells
rngCell.Comment.Visible = True
Next rngCell

End Sub

Does that show the comments?

Gary