Adding Range of Cell Value to Comments

mamun.ges · Jul 26th 2022

Hi All,

I searched many forums but did not get any expected results. Below I am sharing my thoughts, hope someone could help me in this context.

In my worksheet (Worksheet Name Bill), I want Cell "I" will show the value of "W, X & Y"

My Comments range is I9:I1200

My data range is "W, X & Y" from 9 to 1200

In comments the comment will show as " Product ID: "W" value, Product Amount: X value & Product Paid Amount: "Y" Value.

If No data in "W, X & Y" then no comment.

If data changes then the comment updates automatically but no need of old values to show in a comment.

(If the code run in the module will be best for me)

Hope I am able to describe it. Help on this would be much appreciated.

Thanks in advance

davesexcel · Jul 27th 2022

What kind of help did you need?

There are tons of posts in many forums about populating cell comments and loops you must have tried something if you searched through many forums.

mamun.ges · Jul 28th 2022

Hi, Thanks for your feedback.

I need a code for doing the above condition to fulfill.

I found a code & made some changes to the code but failed. (Modified code)

Code

Sub CellToComment()
    Dim xc As Long
    Dim ws As Worksheet
    Set ws = ActiveSheet
    With ws
    For xr = 9 To 1200
    Set CheckRange = Sheets("Bill").Range("W9:Y1200")
    If CheckRange Is Nothing Then
    .Cells(xr, 9).ClearComments
    
    Else
    .Cells(xr, 9).ClearComments
    .Cells(xr, 9).AddComment .Cells(xr, 23).Value & " Product ID:, " & .Cells(xr, 24).Value & " Product Amount:, " & .Cells(xr, 25).Value & " Product Paid Amount:. "


    End If
    Next
    End With
    
    End Sub

Display More

In the above code, it creates the comment for all the data of range ("W9:Y1200). I want a code that creates comments only for those which have value in the range ("W9:Y1200).

Can anyone help in this context?

Hope someone is ready to help which is very appreciating.

Thanks in Advance.

B Rama Krishna · Jul 28th 2022

Hi,

It is not clear on what basis would the comments be updated.

Pl try the code below. It updates the comments in220728 OZgrid Add comments to cells.xlsm Sheets("Sheet1") from I9 to I1000 using the data from W9:Y1000. If this is not what you want, pl send file with sample data for better coding.

Code

Sub CellToComment()

Dim xc As Long

Dim ws As Worksheet

Set ws = ActiveSheet

Dim xr As Integer

Dim CheckRange As Range



Set ws = ActiveWorkbook.Sheets("Sheet1")



With ws

For xr = 9 To 1200

Set CheckRange = Sheets("Bill").Range("W9:Y1200")

If CheckRange Is Nothing Then

.Cells(xr, 9).ClearComments



Else

.Cells(xr, 9).ClearComments

.Cells(xr, 9).AddComment .Cells(xr, 23).Value & " Product ID:, " & .Cells(xr, 24).Value & " Product Amount:, " & .Cells(xr, 25).Value & " Product Paid Amount:. "



End If

Next



End With



End Sub

Display More

**KjBox** · Jul 28th 2022

Try the following

First run this code with the sheet needing comments in Column I active

Code

Sub SetCellComments()
    Dim x, i&
    
    With ActiveSheet
        x = .Cells(9, 23).Resize(1191, 3)
        .Cells(9, 9).Resize(1191).ClearComments
        For i = 1 To UBound(x, 1)
            With .Cells(i + 8, 9)
                If Len(x(i, 1)) And Len(x(i, 2)) And Len(x(i, 3)) Then
                    .AddComment "Product ID: " & x(i, 1) & vbLf & _
                                "Product Amount: " & x(i, 2) & vbLf & _
                                "Product Paid Amount: " & x(i, 1)
                End If
            End With
        Next
    End With

End Sub

Display More

The above code needs to be run just once to initially add required comments, if any, to Column I

The code below will update the comment in Column I for any change in Columns W, X or Y in the same row.

Note this code must be placed in the Sheet Object Module not a standard module

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, ActiveSheet.Cells(9, 23).Resize(1191, 3)) Is Nothing Then
        With ActiveSheet.Cells(Target.Row, 9)
            .ClearComments
            If Len(.Parent.Cells(Target.Row, 23)) _
            And Len(.Parent.Cells(Target.Row, 24)) _
            And Len(.Parent.Cells(Target.Row, 25)) Then
                .AddComment "Product ID: " & .Parent.Cells(Target.Row, 23) & vbLf & _
                            "Product Amount: " & .Parent.Cells(Target.Row, 24) & vbLf & _
                            "Product Paid Amount: " & .Parent.Cells(Target.Row, 25)
            End If
        End With
    End If
    
End Sub

Display More

mamun.ges · Jul 28th 2022

Thanks, KjBox for your kind feedback,

I tried your code but in some cells, it gives the comment but in some cells, it does not though there is value existing in "WXY"

I can't find the problem in your code. Will you recheck the code?

Though I tried to establish a code which result is good but it takes a long time to initiate. The code is below.

I will be happy if you check the below code too.

Code

Sub DataToComment()
Dim LRow As Long, aCell As Range, bCell As Range, cCell As Range, ws As Worksheet
    Set ws = ActiveSheet
      With ws
         For LRow = 9 To .Cells(Rows.Count, 1).End(xlUp).Row
            If .Cells(LRow, 9).Value <> "" Then
                
    Dim theComment As String
                theComment = ""
    Dim xComment As Comment
                                
                For Each aCell In Intersect(Range("W:W"), .Rows(LRow)).Cells
                For Each bCell In Intersect(Range("X:X"), .Rows(LRow)).Cells
                For Each cCell In Intersect(Range("Y:Y"), .Rows(LRow)).Cells
                    theComment = theComment & "Product ID: = " & aCell.Value & " Product Amount: = " & bCell.Value & " Product Paid Amount: = " & cCell.Value
                Next
                    Next
                        Next
    
            With .Cells(LRow, 9)
                .ClearComments
                .AddComment theComment
            
            With .Comment.Shape.TextFrame
            .AutoSize = True
            .Characters.Font.Name = "Times New Roman"
            .Characters.Font.Size = 14
            
            End With
                End With
                    End If
            Next LRow
            End With
End Sub

Display More

Thanks and best regards.

**KjBox** · Jul 28th 2022

Attach a workbook which has just the data you have in columns W, X & Y

mamun.ges · Jul 28th 2022

Hi KjBox, I am Attaching a sample data file Test.xlsm with the VBA code in it.

Please remove the comments in "I" then initiate the command button to see the result.

Please read the Yellow mark sentences to know my expectation.

Thanks again.

**KjBox** · Jul 29th 2022

Try the attached

Code assigned to the button

Code

Sub SetCellComments()
    Dim x, i&
    
    With ActiveSheet.ListObjects(1).DataBodyRange
        x = .Columns(23).Resize(, 3)
        .Columns(9).ClearComments
        For i = 1 To UBound(x, 1)
            With .Columns(9).Rows(i)
                If x(i, 1) <> "" And x(i, 2) <> "" And x(i, 3) <> "" Then
                    .AddComment "Product ID: " & x(i, 1) & vbLf & _
                                "Product Amount: " & x(i, 2) & vbLf & _
                                "Product Paid Amount: " & x(i, 3)
                    With .Comment.Shape.TextFrame
                        .AutoSize = True
                        .Characters.Font.Name = "Times New Roman"
                        .Characters.Font.Size = 14
                    End With
                End If
            End With
        Next
    End With

End Sub

Display More

Note this code needs to be run just once and can then be deleted. It will add the comments to all cells in the Table Column 9.

It will be faster than your code, but even if it takes a few seconds, or even minutes, to run it does not really matter as it never needs to be run again.

The code below is in the Sheet Object Module and will update the Column 9 comment for any change to Columns 23, 24 or 25

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, ActiveSheet.Cells(9, 23).Resize(1991, 3)) Is Nothing Then
        With ActiveSheet.Cells(Target.Row, 9)
            .ClearComments
            If .Parent.Cells(Target.Row, 23) <> "" _
            And .Parent.Cells(Target.Row, 24) <> "" _
            And .Parent.Cells(Target.Row, 25) <> "" Then
                .AddComment "Product ID: " & .Parent.Cells(Target.Row, 23) & vbLf & _
                            "Product Amount: " & .Parent.Cells(Target.Row, 24) & vbLf & _
                            "Product Amount Paid: " & .Parent.Cells(Target.Row, 25)
                With .Comment.Shape.TextFrame
                    .AutoSize = True
                    .Characters.Font.Name = "Times New Roman"
                    .Characters.Font.Size = 14
                End With
            End If
        End With
    End If
    
End Sub

Display More

mamun.ges · Jul 29th 2022

Thanks, KjBox for the help and the beautiful code.

Though I am initially getting a “Run-time 1004 error: Method ‘Sheets’ of object ‘_Global’ failed.”.

But after changing the ActiveSheet. to Me. the error resolves and the code works like a charm.

Best wishes to you and really appreciate the help.

Thanks again.

**KjBox** · Jul 30th 2022

You're welcome

Adding Range of Cell Value to Comments

mamun.ges Jul 29th 2022

Participate now!

Share

Similar Threads

Vba to find a value from a range of returns values of index function

Append New String/Values to pre-existing String/value in a Cell

Update of Partial cell value

adding cell to cell hyperlinks across sheets, how to reference?

Sum Cell Values Until Blank Cells With VBA

How to find location of cells within selection range and referring to them later

Tags