Displaying cell comments.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan N - 20 Jan 2006 13:05 GMT

How do I make a cell display the cell comments only when the cell is active?
I am using Excel 2000.

Reply to this Message

K Dales - 20 Jan 2006 14:46 GMT

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("B4").Comment.Visible = Not (Intersect(Target, Range("B4")) Is
Nothing)
End Sub

Signature

- K Dales

> How do I make a cell display the cell comments only when the cell is active?
> I am using Excel 2000.

Reply to this Message

Dan N - 20 Jan 2006 15:36 GMT

To K Dales - Thank you. That worked great!
If I want to do the same thing in other cells on the same spreadsheet, what
should I do? - Dan N.

> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Range("B4").Comment.Visible = Not (Intersect(Target, Range("B4")) Is
[quoted text clipped - 3 lines]
> > How do I make a cell display the cell comments only when the cell is active?
> > I am using Excel 2000.

Reply to this Message

K Dales - 20 Jan 2006 16:56 GMT

If there are only a few cells with comments, I would just duplicate the line
of code that toggles the comment for each of them:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("B4").Comment.Visible = Not (Intersect(Target, Range("B4")) Is
Nothing)
Range("C5").Comment.Visible = Not (Intersect(Target, Range("C5")) Is
Nothing)
...
End Sub

But if there are many of them, and/or you may add or delete other comments
in the future, the following is a general method to find and toggle all
comments on the sheet - only disadvantage is it runs a bit slower and you may
note the lag as the comments toggle on/off:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ThisCell As Range

For Each ThisCell In Me.Cells.SpecialCells(xlCellTypeComments)
ThisCell.Comment.Visible = Not (Intersect(Target, ThisCell) Is Nothing)
Next ThisCell

End Sub

Signature

- K Dales

> To K Dales - Thank you. That worked great!
> If I want to do the same thing in other cells on the same spreadsheet, what
[quoted text clipped - 7 lines]
> > > How do I make a cell display the cell comments only when the cell is active?
> > > I am using Excel 2000.

Reply to this Message

Dan N - 23 Jan 2006 19:44 GMT

To K Dales - That worked absolutely great! Thank you so much for your help. I
really appreciate it.
- Dan N

> If there are only a few cells with comments, I would just duplicate the line
> of code that toggles the comment for each of them:
[quoted text clipped - 32 lines]
> > > > How do I make a cell display the cell comments only when the cell is active?
> > > > I am using Excel 2000.

Reply to this Message