HELP! PLS!

[Guest]

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!

 

[Guest]

Put this in worksheet code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments)
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.

 

[JE McGimpsey]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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...

 

[Guest]

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.

 

[Guest]

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(xlCellTypeComments)
'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

 

[Guest]

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.

 

[Guest]

OK, I've been going over the code after your response (thank you!) and what I
don't understand is the order in which the code is being executed.

It looks as though the last line of code says to display the comment, but
the code above it says to hide the comment when leaving the cell.

Also, does r mean range? I've been researching and can't seem to find
something that clearly explains this... BTW, I sure see your name out there!

Thank you so much!

 

[Guest]

Because the code does not know which comments are hidden and which comments
are visible the:

r.comment.Visible = False

makes EVERY comment on the sheet hidden. The last part of the code reveals
only the selected cell's comment.

 

[Guest]

Hi again. I tested this again at length today and find that some of my
comments stay displayed, even when I move to another cell. Then, after
awhile I get:
Run-time error '91':
Object variable or With block variable not set

Any help would be GREATLY appreciated!