VBA CommentThreaded 'show'

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,132
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,070
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
Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,785
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,260
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)

Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,260
This is a starting point which you could develop further

Place in SHEET module

Code:
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
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,260
:oops::oops::oops:
I just spotted that I did not copy everything ..

Also goes in sheet module
Code:
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
 

Forum statistics

Threads
1,078,213
Messages
5,338,891
Members
399,265
Latest member
aj17x55

Some videos you may like

This Week's Hot Topics

Top