Displaying cell comments.

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

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

--
- K Dales


"Dan N" wrote:

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

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.

"K Dales" wrote:

> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Range("B4").Comment.Visible = Not (Intersect(Target, Range("B4")) Is
> Nothing)
> End Sub
>
> --
> - K Dales
>
>
> "Dan N" wrote:
>
> > How do I make a cell display the cell comments only when the cell is active?
> > I am using Excel 2000.

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

--
- K Dales


"Dan N" wrote:

> 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.
>
> "K Dales" wrote:
>
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > Range("B4").Comment.Visible = Not (Intersect(Target, Range("B4")) Is
> > Nothing)
> > End Sub
> >
> > --
> > - K Dales
> >
> >
> > "Dan N" wrote:
> >
> > > How do I make a cell display the cell comments only when the cell is active?
> > > I am using Excel 2000.

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

"K Dales" wrote:

> 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
>
> --
> - K Dales
>
>
> "Dan N" wrote:
>
> > 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.
> >
> > "K Dales" wrote:
> >
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > Range("B4").Comment.Visible = Not (Intersect(Target, Range("B4")) Is
> > > Nothing)
> > > End Sub
> > >
> > > --
> > > - K Dales
> > >
> > >
> > > "Dan N" wrote:
> > >
> > > > How do I make a cell display the cell comments only when the cell is active?
> > > > I am using Excel 2000.