Results 1 to 6 of 6

Thread: VBA CommentThreaded 'show'
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default VBA CommentThreaded 'show'

    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?

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,819
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA CommentThreaded 'show'

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA CommentThreaded 'show'

    Quote Originally Posted by Kamolga View Post
    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA CommentThreaded 'show'

    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

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA CommentThreaded 'show'

    This is a starting point which you could develop further

    Place in SHEET module

    Code:
    Private Sub Worksheet_Activate()
        'loop all threaded comments in sheet
        Dim ThreadedComment As CommentThreaded, cel As Range
        For Each ThreadedComment In ActiveSheet.CommentsThreaded
            Call AddTextBox(ThreadedComment)
        Next
    End Sub
    
    Private Sub Worksheet_Deactivate()
        'delete all textboxes
        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)
        'create a text box containing threaded comment
        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 by Yongle; Sep 14th, 2019 at 12:01 PM.

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA CommentThreaded 'show'


    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •