Comments

[Francis Hookham]

I have seen Debra's Contextures - Comments which helped with the following

Sub CommentAdd()
Dim cmt As Comment
Set cmt = ActiveCell.Comment
If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.Text Text:=""
End If
With cmt.Shape.TextFrame.Characters.Font
.name = "Arial"
.Size = 12
.Bold = False
.ColorIndex = 0
End With
cmt.Visible = True
cmt.Shape.Select
End Sub

which leaves the comment open so I am using the following to close it

Sub CommentHide()
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End Sub

OK but I should likea toggle button like the Reviewing Show/Hide all
comments button which would sence that there is one comment showing and
close it. Someting like this (which does not work)

Sub CommentHideShow()
If ActiveSheet.Comments.Visible = True Then
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
Else
ActiveSheet.Comments.Visible = True
End If
End Sub

Any ideas please and thanks

Francis Hookham

 

[Roger Govier]

Hi Francis

Could you not use

Sub CommentHideShow()
If Application.DisplayCommentIndicator = xlCommentIndicatorOnly Then
Application.DisplayCommentIndicator = xlCommentAndIndicator
Else
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End If
End Sub

 

[Francis Hookham]

Thanks Roger but that is the same as the Reviewing toolbar Show/Hide button.
I was hoping that, when there was the one commet open following the first
macro below, it would be sensed and be closed. At present Sub
CommentHideShow needs to be clicked twice - to open the rest and then close
all.

Please don't worry about it - it was just the sort of refinement one enjoys
wasting time on!

Thanks

Francis

 

[Francis Hookham]

Very strange Roger - I thought I resonded, in fact it is in my Sent Items
folder but does not show in the menews folder - anyway this is a copy:

Thanks Roger but that is the same as the Reviewing toolbar Show/Hide button.
I was hoping that, when there was the one commet open following the first
macro below, it would be sensed and be closed. At present Sub
CommentHideShow needs to be clicked twice - to open the rest and then close
all.

Please don't worry about it - it was just the sort of refinement one enjoys
wasting time on!

Thanks

Francis

 

[hakapes]

xlCommentAndIndicator

This script works for me, I assigned it to a new button. It works like an on/off switch to show/hide comment-boxes.

Sub ShowHideComments()
'
' ShowHideComments Macro
' Macro recorded 2009.02.09 by hakapes
'
' Keyboard Shortcut: Ctrl+q
'
If Application.DisplayCommentIndicator <> xlCommentAndIndicator Then
Application.DisplayCommentIndicator = xlCommentAndIndicator
Else
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End If

End Sub