Multiple cells into comment

[Toomicek]

Hi there,

I want to add text from 3-5 cells in different sheets to one cell like commentary. I am not a pro in VBA so i need help

Added sample

Thanks

Thomas

 

[Toomicek]

Anyone?

 

[Peter_SSs]

I'm not sure I can help anyway but your requirement is not clear to me and maybe that is also why others are not responding.

Your screen shot sort of makes sense but your written description doesn't match it as that referred to cells on different sheets. Please clarify.

 

[Toomicek]

I'm not sure I can help anyway but your requirement is not clear to me and maybe that is also why others are not responding.

Your screen shot sort of makes sense but your written description doesn't match it as that referred to cells on different sheets. Please clarify.

Lets assume that all those input charts are on different sheets or if you can make it to work on one sheet I can adjust it for my needs

In short: Can you make it work like it is on screenshot?


Thank you for reply

 

[Peter_SSs]

In short: Can you make it work like it is on screenshot?

Thomas, that is what I have attempted - for that exact layout but monitoring for changes in G16:G20.

Test in a copy of your workbook.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim s As String, cmt As String, sBold As String
  Dim aTLC As Variant, aData As Variant, aBold As Variant
  Dim i As Long, Tbl As Long
  Dim bStarted As Boolean
  
  Const TopLeftCells As String = "A1 D1 G1" '<- Top left cell of each table
  Const CommentCells As String = "G16:G20"  '<- Range to add/delete/edit comments
  
  Set Changed = Intersect(Target, Range(CommentCells))
  If Not Changed Is Nothing Then
    aTLC = Split(TopLeftCells)
    For Each c In Changed
      c.ClearComments
      s = c.Text
      If Len(s) > 0 Then
        For Tbl = 0 To UBound(aTLC)
          aData = Range(aTLC(Tbl)).CurrentRegion.Value
          bStarted = False
          For i = 2 To UBound(aData, 1)
            If aData(i, 1) = s Then
              If Not bStarted Then
                sBold = sBold & "," & Len(cmt) + 1 & "," & Len(aData(1, 2))
                bStarted = True
                cmt = cmt & vbLf & aData(1, 2)
              End If
              cmt = cmt & vbLf & aData(i, 2)
            End If
          Next i
        Next Tbl
        If Len(cmt) > 0 Then
          c.AddComment.Text Text:=Mid(cmt, 2)
          aBold = Split(sBold, ",")
          For i = 1 To UBound(aBold) Step 2
            c.Comment.Shape.TextFrame.Characters(aBold(i), aBold(i + 1)).Font.Bold = True
          Next i
          c.Comment.Shape.TextFrame.AutoSize = True
        End If
      End If
    Next c
  End If
End Sub

 

[Toomicek]

Thomas, that is what I have attempted - for that exact layout but monitoring for changes in G16:G20.

Test in a copy of your workbook.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim s As String, cmt As String, sBold As String
  Dim aTLC As Variant, aData As Variant, aBold As Variant
  Dim i As Long, Tbl As Long
  Dim bStarted As Boolean
  
  Const TopLeftCells As String = "A1 D1 G1" '<- Top left cell of each table
  Const CommentCells As String = "G16:G20"  '<- Range to add/delete/edit comments
  
  Set Changed = Intersect(Target, Range(CommentCells))
  If Not Changed Is Nothing Then
    aTLC = Split(TopLeftCells)
    For Each c In Changed
      c.ClearComments
      s = c.Text
      If Len(s) > 0 Then
        For Tbl = 0 To UBound(aTLC)
          aData = Range(aTLC(Tbl)).CurrentRegion.Value
          bStarted = False
          For i = 2 To UBound(aData, 1)
            If aData(i, 1) = s Then
              If Not bStarted Then
                sBold = sBold & "," & Len(cmt) + 1 & "," & Len(aData(1, 2))
                bStarted = True
                cmt = cmt & vbLf & aData(1, 2)
              End If
              cmt = cmt & vbLf & aData(i, 2)
            End If
          Next i
        Next Tbl
        If Len(cmt) > 0 Then
          c.AddComment.Text Text:=Mid(cmt, 2)
          aBold = Split(sBold, ",")
          For i = 1 To UBound(aBold) Step 2
            c.Comment.Shape.TextFrame.Characters(aBold(i), aBold(i + 1)).Font.Bold = True
          Next i
          c.Comment.Shape.TextFrame.AutoSize = True
        End If
      End If
    Next c
  End If
End Sub

That works very well THANKS!!!

Imagine if you can set it up for change in input charts? Like if A1:H6 changes than do new comments?

 

[Peter_SSs]

Imagine if you can set it up for change in input charts? Like if A1:H6 changes than do new comments?

Rather than start over again, I think a quick fix would be to change the existing red line below into the blue one. Give that a try.

Set Changed = Intersect(Target, Range(CommentCells))
Set Changed = Range(CommentCells)

 

[Toomicek]

Rather than start over again, I think a quick fix would be to change the existing red line below into the blue one. Give that a try.

Set Changed = Intersect(Target, Range(CommentCells))
Set Changed = Range(CommentCells)

This didnt work. This copies previous comment in it Moved it and used

Const CommentCells As String = "J4:J8"

Maybe i will use button to get comments like (REFRESH) or something so users can refresh it and see comments.

 

[Peter_SSs]

This didnt work. This copies previous comment in it

1. Just confirming, from your image mainly, that what you are saying is that with the post #7 change, when you change values in the tables that the original comment stays, but has more text added to it?

2. What version of Excel are you using?

3. Can you also confirm whether the values in the tables are changed manually or are they formulas?

 

[Toomicek]

1. Just confirming, from your image mainly, that what you are saying is that with the post #7 change, when you change values in the tables that the original comment stays, but has more text added to it?

2. What version of Excel are you using?

3. Can you also confirm whether the values in the tables are changed manually or are they formulas?

1. When I moddified original code with #7 edit, than when I changed anything in tables, it generates new comments with last one showing all previous data. "NOT INTEDNED"
2.Excel 2013
3.They are changed manualy by user