Comment shall display Cell values of another worksheet

[dasbroth]

Hi all,

I am basicly looking for a way to display reasons for a delay that are stored in Sheet1 and Sheet2 (Columns H and I) in worksheet3, when someone moves over or clicks on the delay time in worksheet3 (E8, E9,...) .

Meaning that every comment is different.

Having googelt and browsed here, I found out that this might be done via macro and comment.

Can someone help me with this as I have no idea how to program it.

Attached I have inserted a rough draft of my excel document, also having visualized what I`d like to see.
If you have any questions, just write them down here. I will answer asap.

Thanks and best regards

[tigeravatar]

dasbroth,

The following macro will update comments as desired (based on the example workbook you provided). To make it work in the real one, update the sheetnames in the code. The columns for rngArr and rngDep (currently E and L) may also need to be adjusted if they are not in the same columns as the example workbook. This macro runs when a button is clicked that then updates all the comments. The button only needs to be clicked if a comment needs to be updated or added. I've attached a modified version of your example workbook so you can see how it works.

Sub UpdateComments()

    Dim wsArr As Worksheet: Set wsArr = Sheets("Sheet1")
    Dim wsDep As Worksheet: Set wsDep = Sheets("Sheet2")
    
    Dim rngArr As Range:    Set rngArr = ActiveSheet.Range("E8:" & Cells(Rows.Count, 5).End(xlUp).Address)
    Dim rngDep As Range:    Set rngDep = ActiveSheet.Range("L8:" & Cells(Rows.Count, 12).End(xlUp).Address)
    Dim ArrCell As Range, DepCell As Range, strComment As String, cIndex As Long
    
    cIndex = 1
    For Each ArrCell In rngArr
        cIndex = cIndex + 1
        strComment = wsArr.Range("H" & cIndex).Value & " - " & wsArr.Range("I" & cIndex).Value
        On Error Resume Next
        ArrCell.Comment.Delete
        ArrCell.AddComment Text:=strComment
        ArrCell.Comment.Shape.TextFrame.AutoSize = True
    Next ArrCell
    
    cIndex = 1
    For Each DepCell In rngDep
        cIndex = cIndex + 1
        strComment = wsDep.Range("H" & cIndex).Value & " - " & wsDep.Range("I" & cIndex).Value
        On Error Resume Next
        DepCell.Comment.Delete
        DepCell.AddComment Text:=strComment
        DepCell.Comment.Shape.TextFrame.AutoSize = True
    Next DepCell
    
End Sub

Hope that helps,
~tigeravatar

[dasbroth]

Wow, thank you very much!!!!
This is really great!!!

Can you tell me, how the macro needs to be changed in order to display the comments not only in one line, but also in two lines?

Again, thank you!

Sebastian

[tigeravatar]

Sebastian,

I'm not entirely sure what you mean. Isn't the macro already putting comments down two columns? Or are you looking for something else?

~tigeravatar

[dasbroth]

Yeah, you are right.

Maybe I did explain it bad today.

What I would like to have is that the comment box is displayed in two rows, like in the attached image.

the Macro pulls all the information in one line.

Thanks and regards,
Sebastian

[snb]

or:

Sub snb()
  sq = Sheets("sheet1").Cells(1, 8).CurrentRegion
  sn = Sheets("sheet2").Cells(1, 8).CurrentRegion
    
  For Each cl In Sheets("sheet3").Columns(5).SpecialCells(2, 1)
    cl.Comment.Text sq(cl.Row - 6, 1) & "-" & vbLf & sq(cl.Row - 6, 2)
  Next
    
  For Each cl In Sheets("sheet3").Columns(12).SpecialCells(2, 1)
    cl.Comment.Text sn(cl.Row - 6, 1) & "-" & vbLF & sn(cl.Row - 6, 2)
  Next
End Sub