Comment position after filter
 

Hi everybody -
I have some comments in my Excel 2000 spreadsheet. When I filter the spreadsheet, the comments do not change position with the cells and end up far from them? Is there any way I can work around this, eg. by specifying an event in VB?
Thanks,
Cassie.

You need to be clearer.
I'm assuming that you are referring to the 'Comment' object that can be
added to a cell.
Cells do not change position when you filter. They are hidden and
unhidden but cell 'H7' (for example) will stay 'H7' whether it is a part of
your filter or not. The comment also stays with the cell at 'H7'.
When sorting, the comment moves with the cell, so if 'H7' re-sorts to
'H2', the comment that was on 'H7' is now located on 'H2'.

If you're talking about sorting and some text written in a cell, check to
make sure that the comment cell is within the range of the sort. For
example, if your text is in Cell 'H7' but you are sorting only cells 'A1'
thru 'C10', you WILL loose the positioning of the 'comment' cell.

Hope one of these suggestions is what you're looking for.
--
Gary Brown

If this post was helpful, please click the ''''''''Yes'''''''' button next
to ''''''''''''''''Was this Post Helpfull to you?".


"cassie" wrote:


Hi everybody -
I have some comments in my Excel 2000 spreadsheet. When I filter the
spreadsheet, the comments do not change position with the cells and end
up far from them? Is there any way I can work around this, eg. by
specifying an event in VB?
Thanks,
Cassie.


--
cassie

Saved from a previous response...

If your data is unfiltered, then the comments show up nicely--both when hovering
over the cell and when editting.

But if you filter the range, then the comment is ok when you hover over the
cell--but not when you edit the comment.

How about just fixing the comments that are associated with visible cells?

Option Explicit
Sub ResetComments()

Dim cmt As Comment

For Each cmt In ActiveSheet.Comments
If cmt.Parent.EntireRow.Hidden Then
'do nothing
Else
cmt.Shape.Top = cmt.Parent.Top + 5
cmt.Shape.Left = _
cmt.Parent.Offset(0, 1).Left + 5
End If
Next cmt

End Sub

This worked until I did a showall or filtered using a different criteria.



cassie wrote:

Hi everybody -
I have some comments in my Excel 2000 spreadsheet. When I filter the
spreadsheet, the comments do not change position with the cells and end
up far from them? Is there any way I can work around this, eg. by
specifying an event in VB?
Thanks,
Cassie.

--
cassie

--

Dave Peterson