VBA CommentThreaded 'show'

[Kamolga]

Hi,
I would like to show a threaded comment (with notes it is straightforward, we put them on 'visible=true') when activating a sheet.
Is there a line of code to show those threaded comments ?

I noticed there is a "show comments" in review tab but recording macro does not help. Even if I add a comment to the thread through VBA, it does not appear to user without going with the mouse on the cell.

Any idea?

 

[My Aswer Is This]

If your saying you want all cell comments to be visible when you activate a sheet then try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Activate()
'Modified 9/13/2019 10:02:09 PM  EDT
Dim c As Comment
Application.ScreenUpdating = False
    For Each c In ActiveSheet.Comments
        c.Visible = True
    Next
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Hi,
I would like to show a threaded comment ..

As you have found 'Threaded Comments' behave differently to the old 'Comments' (now called 'Notes').

From this page comes the statement ..

Unlike Notes, you can’t show multiple Comments in the worksheet at the same time.

 

[Yongle]

I would like to show a threaded comment when activating a sheet

You could use VBA to do this by placing the text in a textbox
- here is a function to get at the detail (amend if you need more information)

Sub TestFunction()
    MsgBox GetThreadedComment(ActiveCell)
End Sub

Function GetThreadedComment(cel As Range) As String
    On Error Resume Next
    Dim Cmt As String, x As Long
    Const S = " : ", L = vbCr
    With cel.CommentThreaded
        Cmt = cel.Address(0, 0) & L & .Author.Name & S & .Text
        For x = 1 To .Replies.Count
            Cmt = Cmt & L & .Replies(x).Author.Name & S & .Replies(x).Text
        Next x
    End With
    If Len(Cmt) = 0 Then Cmt = "No Comment"
    GetThreadedComment = Cmt
End Function

Place each Threaded Comment in a textbox
Use worksheet activate event to display testboxes
Either create textboxes "on the hoof" and then delete or show existing textboxes and then hide

 

[Yongle]

This is a starting point which you could develop further

Place in SHEET module

Private Sub Worksheet_Activate()
 [COLOR=#006400]   'loop all threaded comments in sheet[/COLOR]
    Dim ThreadedComment As CommentThreaded, cel As Range
    For Each ThreadedComment In ActiveSheet.CommentsThreaded
        Call AddTextBox(ThreadedComment)
    Next
End Sub

Private Sub Worksheet_Deactivate()
[COLOR=#006400]    'delete all textboxes[/COLOR]
    Dim obj As OLEObject
    For Each obj In Me.OLEObjects
        If obj.OLEType = 2 Then obj.Delete
    Next
End Sub

Private Sub AddTextBox(TC As CommentThreaded)
  [COLOR=#006400]  'create a text box containing threaded comment[/COLOR]
    Dim oTB As Object, cel As Range
    Set cel = TC.Parent
    Set oTB = Me.OLEObjects.Add(ClassType:="Forms.TextBox.1")
    With oTB
        .Left = cel.Offset(, 1).Left
        .Top = cel.Top
        .Width = 200
        .Height = 200
        .Object.Text = GetCommentText(TC)
        .Object.MultiLine = True
    End With
End Sub

 

[Yongle]

I just spotted that I did not copy everything ..

Also goes in sheet module

Private Function GetCommentText(TC As CommentThreaded) As String
    Dim Cmt As String, x As Long
    Const S = " : ", L = vbCr
    With TC
        Cmt = .Author.Name & S & .Text
        For x = 1 To .Replies.Count
            Cmt = Cmt & L & .Replies(x).Author.Name & S & .Replies(x).Text
        Next x
    End With
    GetCommentText = Cmt
End Function