I am trying to make the comments I add to cells in my worksheet appear when I
tab or click in a cell, as opposed to having to point with the mouse.

For various reasons, data validation message boxes don't work (limitations
in number of characters, etc.)

Here is my code (I am NOT experienced in VBA, which you can tell). I keep
getting errors. I want to say: In the active sheet (or workbook, or even
range!) when I click or tab to a cell, display the comment.

ub DisplayComments()

If activecell.comment Is Nothing Then
'do nothing
Else
comment.Visible = True
End If

Thanks for your help!
--
Thanks!

Dee

Put this in worksheet code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts)
r.Comment.Visible = False
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
Target.Comment.Visible = True
End Sub


REMEMBER: worksheet code, not a standard module.
--
Gary''s Student - gsnu200720

Thank you so much. It worked like a charm. No need to pull the rest of my
hair out! :-)

--
Thanks!

Dee


"Gary''s Student" wrote:

Put this in worksheet code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts)
r.Comment.Visible = False
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
Target.Comment.Visible = True
End Sub


REMEMBER: worksheet code, not a standard module.
--
Gary''s Student - gsnu200720

I have another question. I've just been testing your code and it works very
well. However, it seem that now whenever I click in a cell, the hourglass is
displayed for a few seconds.

Is there any way to stop this? I'm not sure why it's doing it...


--
Thanks!

Dee


"Gary''s Student" wrote:

Put this in worksheet code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts)
r.Comment.Visible = False
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
Target.Comment.Visible = True
End Sub


REMEMBER: worksheet code, not a standard module.
--
Gary''s Student - gsnu200720

The hour glass probably comes from:

r.Comment.Visible = False

this line restores the comments to hidden when you leave the cell. If you
remove this line the routine will run much faster, but the comments will
remain visible when the cell is left.
--
Gary''s Student - gsnu20072

Got it. I've been spending a lot of time trying to figure this code out and
think I have part of it. If you have time, would you mind looking at my
comments to tell me if I'm totally off track? There are xxx s where I don't
have a clue! Thank you.

Private Sub Worksheet_SelectionChange(ByVal Target As range)
'Private tells VBA to hide the sub from other programming elements (sub will
not appear in macro list either)
'Worksheet_SelectionChange tells the sub to "fire" whenever a cell is
selected in the worksheet
'ByVal Target As range


Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts)
'Sets range as being on the active sheet, cells on worksheet being used,
display cell comment

r.comment.Visible = False
'Restores the comments status back to hidden when you leave the cell

If Intersect(Target, r) Is Nothing Then

Exit Sub
'If xxxx , then exit the sub

End If

Target.comment.Visible = True
'Otherwise, when move to cell, display any comments

End Sub
--
Thanks!

Dee


"Gary''s Student" wrote:

The hour glass probably comes from:

r.Comment.Visible = False

this line restores the comments to hidden when you leave the cell. If you
remove this line the routine will run much faster, but the comments will
remain visible when the cell is left.
--
Gary''s Student - gsnu20072

You are right on track !! The intersect thing just compares the currently
selected cell with the collection of cells that have comments. If the
currently selected cell is not part of the collection, then the currently
selected cell has no comments in it and the routine can exit.
--
Gary''s Student - gsnu200720


"dee" wrote:

Got it. I've been spending a lot of time trying to figure this code out and
think I have part of it. If you have time, would you mind looking at my
comments to tell me if I'm totally off track? There are xxx s where I don't
have a clue! Thank you.

Private Sub Worksheet_SelectionChange(ByVal Target As range)
'Private tells VBA to hide the sub from other programming elements (sub will
not appear in macro list either)
'Worksheet_SelectionChange tells the sub to "fire" whenever a cell is
selected in the worksheet
'ByVal Target As range


Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComme nts)
'Sets range as being on the active sheet, cells on worksheet being used,
display cell comment

r.comment.Visible = False
'Restores the comments status back to hidden when you leave the cell

If Intersect(Target, r) Is Nothing Then

Exit Sub
'If xxxx , then exit the sub

End If

Target.comment.Visible = True
'Otherwise, when move to cell, display any comments

End Sub
--
Thanks!

Dee


"Gary''s Student" wrote:

The hour glass probably comes from:

r.Comment.Visible = False

this line restores the comments to hidden when you leave the cell. If you
remove this line the routine will run much faster, but the comments will
remain visible when the cell is left.
--
Gary''s Student - gsnu20072

One way:

Put this in your worksheet code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim cm As Comment
Static bVisibleComment As Boolean

If bVisibleComment Then
For Each cm In ActiveSheet.Comments
cm.Visible = False
Next cm
End If
If Not ActiveCell.Comment Is Nothing Then
ActiveCell.Comment.Visible = True
bVisibleComment = True
End If
End Sub


In article ,
dee wrote:

I am trying to make the comments I add to cells in my worksheet appear when I
tab or click in a cell, as opposed to having to point with the mouse.

For various reasons, data validation message boxes don't work (limitations
in number of characters, etc.)

Here is my code (I am NOT experienced in VBA, which you can tell). I keep
getting errors. I want to say: In the active sheet (or workbook, or even
range!) when I click or tab to a cell, display the comment.

ub DisplayComments()

If activecell.comment Is Nothing Then
'do nothing
Else
comment.Visible = True
End If

Thanks for your help!

Thank you for your response. I tried this, but it didn't seem to work. I
must be doing something wrong.


--
Thanks!

Dee


"JE McGimpsey" wrote:

One way:

Put this in your worksheet code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim cm As Comment
Static bVisibleComment As Boolean

If bVisibleComment Then
For Each cm In ActiveSheet.Comments
cm.Visible = False
Next cm
End If
If Not ActiveCell.Comment Is Nothing Then
ActiveCell.Comment.Visible = True
bVisibleComment = True
End If
End Sub


In article ,
dee wrote:

I am trying to make the comments I add to cells in my worksheet appear when I
tab or click in a cell, as opposed to having to point with the mouse.

For various reasons, data validation message boxes don't work (limitations
in number of characters, etc.)

Here is my code (I am NOT experienced in VBA, which you can tell). I keep
getting errors. I want to say: In the active sheet (or workbook, or even
range!) when I click or tab to a cell, display the comment.

ub DisplayComments()

If activecell.comment Is Nothing Then
'do nothing
Else
comment.Visible = True
End If

Thanks for your help!