Not showing Comments,when using arrow keys.

[DaveM]

Hi all

The comments appear when I hover over cells, Should they appear when I use
the arrow keys? mine don't.
I have them set on "Comments indicator only"

The reason for this post, Is there a way of showing comments when I use this
Macro.

Sub Finddown()

Dim FoundCell As Range
Set FoundCell = ActiveCell.EntireColumn.Find(What:=ActiveCell.Value, _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not FoundCell Is Nothing Then
FoundCell.Select

End If

End Sub

This macro finds the next same value down the column, I also have a Findup
macro. Is there a way to showing comments when I use this Macro.

Example
To work like this
Active cell is M4, run macro to find next same value down, lets say M50
which there is a comment, it does not show the comment. only on hover.

Thanks in advance

Dave

 

[Tom Ogilvy]

here are commands to show and hide comments

ActiveCell.Comment.Visible = True

ActiveCell.Comment.Visible = False

If you show it this way, you have to close it.

Maybe something like this:

Sub Finddown()
Static LastCell as Range
Dim FoundCell As Range

Set FoundCell = ActiveCell.EntireColumn.Find(What:=ActiveCell.Value, _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

If Not FoundCell Is Nothing Then
If not LastCell is nothing then
set cmt = nothing
On Error Resume NExt
set cmt = lastcell.Comment
On Error goto 0
if not cmt is nothing then
cmt.Visible = False
end if
End if
FoundCell.Select
set lastcell = FoundCell
set cmt = nothing
On error resume Next
set cmt = ActiveCell.Comment
On error goto 0
if not cmt is nothing then
cmt.Visible = True
end if
End If
End Sub

 

[DaveM]

Hi Tom

The code shows the comments but I have to page right, there about 12 inches
away over in column Z.
I've tried adjusting but its not working for me. any idea's to see the
comments along sides the cell.

Thanks for you help Tom

Dave

 

[Tom Ogilvy]

check out Debra's site for voluminous information on comments:

http://www.contextures.com/tiptech.html

specifically: http://www.contextures.com/xlcomments03.html#Reset

Recognize that a comment is a shape just like any other shape from the
drawing toolbar - in fact it is a rectangle and you can use code to make it
one of the other shapes - not what you are after, but you should be able to
manipulate it as you wish.

Demo'd from the immediate window with 1 comment on the sheet (no other
objects)

? activesheet.shapes.count
1
? activesheet.shapes(1).Name
Comment 1
? activesheet.shapes(1).Top
30.75
? activesheet.shapes(1).Left
203.25

 

[DaveM]

Thanks Tom

I found this macro that did the trick, on contextures.com

Reset Comments to Original Position

If comments have moved out of position, you can reset them using the
following code:

Sub ResetComments()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
cmt.Shape.Top = cmt.Parent.Top + 5
cmt.Shape.Left = _
cmt.Parent.Offset(0, 1).Left + 5
Next
End Sub
Thanks

Dave