Link comment to cell contents on a different sheet

[SLARRA]

I would like to automatically compose a comment for a cell based on the contents of another cell on a different worksheet. I realize this will require VBA, which is outside my skill set. I searched this forum and the Web and found several potentially relevant posts, but I was unable to successfully adapt those examples either due to problems with the posted code or (more likely) my ignorance.

Seems like this should be fairly straightforward....

On a worksheet named "Define", there is a small table that occupies C11:E20. In C11:C20, there are short text fields that are used as labels for rating elements (e.g. "Element1" in C11, "Element2" in C12, etc). In D11:D20, there are longer text fields that provide a longer name for the corresponding labels in C11:C20 (e.g. "Element 1 - Long Term Factors" in C11, "Element 2 - Short Term Factors" in C12, etc). In E11:E20, there are longer text fields that describe those corresponding elements in detail (e.g. "Element 1 is comprised of blah, blah, blah..." in E11, "Element 2 includes yadda, yadda, yadda..." in E12, etc).

On a worksheet named "Classify", there is a large table that uses those short labels as a column headers in K18:T18 (e.g. K18 = Define!D11, rendering as "Element1", L18 = Define!D12, rendering as "Element2", etc). Note the transposed orientation between the two worksheets.

I would like to automatically create/link comments for cells K18:T18 on the "Classify" worksheet that replicate the corresponding element's description text from the "Define" worksheet. That is, I want the comment for Classify!K18 (whose cell value is "Element1", matching what's in Define!C11) linked to display the text that's in Define!E11 ("... blah, blah, blah"), Classify!L18's comment linked to Define!E12, etc.

Bonus items:
1. It would be great if those comments were automatically updated if their descriptions (Classify!E11:E20) are edited.
2. Given my arrangement, the description text for the comments could be extracted via Index & Match technique rather than simply referring to an absolute cell reference.
3. It would be great if the comments were composed of the concatenation of the long name (in bold font), followed by the description text (normal font) on the line below. For example, the comment in Classify!K18 rendered as

Element 1 - Long Term Factors:
Element 1 is comprised of blah, blah, blah..​

I can forgo the bonus items if they're tricky.

Thanks in advance!

SDL

 

[James006]

Hello,

Regarding cell comments ... take a look at Debra's excellent explanation :

http://www.contextures.com/xlcomments03.html

Hope this will help

 

[SLARRA]

I would like to automatically compose a comment for a cell based on the contents of another cell on a different worksheet. I realize this will require VBA, which is outside my skill set. I searched this forum and the Web and found several potentially relevant posts, but I was unable to successfully adapt those examples either due to problems with the posted code or (more likely) my ignorance.

Seems like this should be fairly straightforward....

On a worksheet named "Define", there is a small table that occupies C11:E20. In C11:C20, there are short text fields that are used as labels for rating elements (e.g. "Element1" in C11, "Element2" in C12, etc). In D11:D20, there are longer text fields that provide a longer name for the corresponding labels in C11:C20 (e.g. "Element 1 - Long Term Factors" in C11, "Element 2 - Short Term Factors" in C12, etc). In E11:E20, there are longer text fields that describe those corresponding elements in detail (e.g. "Element 1 is comprised of blah, blah, blah..." in E11, "Element 2 includes yadda, yadda, yadda..." in E12, etc).

On a worksheet named "Classify", there is a large table that uses those short labels as a column headers in K18:T18 (e.g. K18 = Define!C11, rendering as "Element1", L18 = Define!C12, rendering as "Element2", etc). Note the transposed orientation between the two worksheets.

I would like to automatically create/link comments for cells K18:T18 on the "Classify" worksheet that replicate the corresponding element's description text from the "Define" worksheet. That is, I want the comment for Classify!K18 (whose cell value is "Element1", matching what's in Define!C11) linked to display the text that's in Define!E11 ("... blah, blah, blah"), Classify!L18's comment linked to Define!E12, etc.

Bonus items:
1. It would be great if those comments were automatically updated if their descriptions (Classify!E11:E20) are edited.
2. Given my arrangement, the description text for the comments could be extracted via Index & Match technique rather than simply referring to an absolute cell reference.
3. It would be great if the comments were composed of the concatenation of the long name (in bold font), followed by the description text (normal font) on the line below. For example, the comment in Classify!K18 rendered as

Element 1 - Long Term Factors:
Element 1 is comprised of blah, blah, blah..​

I can forgo the bonus items if they're tricky.

Thanks in advance!

SDL

Thank you, James.

I had already consulted that excellent resource on VBA techniques for comments, and I see where it includes examples that will allow me to control the formatting of the comment text (Bonus Item #3 above). But, I don't see any examples that will help me with my fundamental need.

Note: In the quoted text from my original post, I have corrected a minor misstatement. If you're new to this thread, please rely on the description of my problem in the quoted text above. I hope my overly verbose description hasn't deterred any responses for that is probably a fairly simple problem - that is, to how to automatically create comments on one sheet that are composed of/linked to the contents of cells on another sheet.

Any further assistance would be much appreciated!

SDL

 

[SLARRA]

I'm still hoping for some help.

Perhaps someone can help me by adapting the code on this thread so that the source cells for the comment contents are drawn from another worksheet (same workbook tho).


Or, this other thread has a solution proposed by the usually reliable Andrew Poulsom that seems to address my needs; however, I get an error from the code that says "Run-time error '91': Object variable or With block variable not set". Maybe it simply needs variables defined or some other trivial matter that still exceeds my comprehension...?

Any help would be appreciated!

SDL

 

[SLARRA]

OK... After far longer than I care to admit, I kinda figured this out myself. I resolved the Run-time error '91' problem arising from Andrew Poulsom's suggestion on that other thread. That error apparently was caused because the cells whose comments I wanted linked to the contents of cells on another worksheet did not already have comments inserted. That is, Andrew's code updates the comment contents, but it does not create comments if they do not already exist. So, I simply needed to manually add (blank) comments to those cells first, then the code executes without error, updating the comments on the "Classify" sheet whenever their source cells are edited on the "Define" sheet.

Here's that code I created and inserted on the module for the sheet named "Define":

Private Sub Worksheet_Change(ByVal Target As Range)
    With Worksheets("Classify")
        Select Case Target.Address
            Case "$E$11"
                .Range("$K$18").Comment.Text Text:=Target.Value
            Case "$E$12"
                .Range("$L$18").Comment.Text Text:=Target.Value
            Case "$E$13"
                .Range("$M$18").Comment.Text Text:=Target.Value
            Case "$E$14"
                .Range("$N$18").Comment.Text Text:=Target.Value
            Case "$E$15"
                .Range("$O$18").Comment.Text Text:=Target.Value
            Case "$E$16"
                .Range("$P$18").Comment.Text Text:=Target.Value
            Case "$E$17"
                .Range("$Q$18").Comment.Text Text:=Target.Value
            Case "$E$18"
                .Range("$R$18").Comment.Text Text:=Target.Value
            Case "$E$19"
                .Range("$S$18").Comment.Text Text:=Target.Value
            Case "$E$20"
                .Range("$T$18").Comment.Text Text:=Target.Value
        End Select
    End With
End Sub

This code above solves my fundamental need - namely, for cells displaying an item's short name on the "Classify" sheet, it automatically inserts the item's definition from the "Define" sheet into a comment box, plus it updates that comment whenever the definition/source text is edited. Note that the code above refers to absolute cell addresses for the source data (it does NOT lookup those definitions, so there is a danger that the rendered comment may not properly correspond to the item name if the user edits the item names on the Classify sheet or moves those cells around).

Now, I'm still hoping for some help on the Bonus Item #3 - namely, having those comments comprised of the concatenation of 2 cells' values, so that a comment's first line will the item's long, descriptive name (derived from Classify!D11:D20) in bold font; and its second line will be the item's definition (derived from Classify!E11:E20) in regular font. I might be able to figure out the formatting bit, but I especially need help with the code that will perform the concatenation. Also, I'm open to any other method that would be better, if what I've done is dicey.

Thanks,

SDL

 

[James006]

Hello,

As indicated in Debra's excellent explanation :

http://www.contextures.com/xlcomments03.html

Concatenation will require Chr(10)

and Characters(Start:=x, Length:=xx) to get Bold ...

Hope this will help

 

[SLARRA]

Yes - Thank you. I had looked through her many excellent examples, and I'm reasonably confident I can handle the code for the comment formatting. What I cannot figure out is the VBA coding that will acquire the comment text from 2 different source cells on another worksheet. If her postings include an example that does THAT, I am unable to recognize it.

 

[James006]

...descriptive name (derived from Classify!D11:D20) in bold font; and its second line will be the item's definition (derived from Classify!E11:E20) in regular font...

It would appear both items are located in the same worksheet source : Classify ...

Should you have two different worksheets as sources ... your Worksheet_Change() event can handle both worksheets .. e.g. ws1 and ws2

Should you need to prepare your comment beforehand ... you can concatenate in Column F your initial texts located in column D and E ...

Hope this will help

 

[SLARRA]

I'm thankful for your help, James, but I really don't know anything about VBA so specific syntax for the code suggestions would be even more helpful.

I finally came up with the following, which works to concatenate the comment elements:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Worksheets("Classify")
        Select Case Target.Address
        'Re-compose Item #1's comment in Classify worksheet cell K18
            'If user makes a change to Item #1's Name in Define Sheet's cell D11
                Case "$D$11"
                    .Range("$K$18").Comment.Text Text:=Target.Value & Chr(10) & Range("$E$11")
            'If user makes a change to Item #1's Description in Define Sheet's cell E11
                Case "$E$11"
                    .Range("$K$18").Comment.Text Text:=Range("$D$11") & Chr(10) & Target.Value
        'Repeat process for Item #2
                Case "$D$12"
                    .Range("$L$18").Comment.Text Text:=Target.Value & Chr(10) & Range("$E$12")
                Case "$E$12"
                    .Range("$L$18").Comment.Text Text:=Range("$D$12") & Chr(10) & Target.Value
        'Need to add more like the above to handle Items #3-10's comments in Classify Sheet's M18-U18
        End Select
    End With
End Sub

I suspect my method is far from optimal, but it works to achieve a couple of my objectives.

Now, I'd still like that first line in the comments in bold font, but all the VBA examples I found seem surprisingly complicated, setting each comment section as string variables, detecting their length, modifying the comment shape text frame properties for that length of text, etc. Is there no way to have my code simply specify the comment text formatting at the same time it's composing the comment using those 2 source elements? That is, have code that will (after a change event that detects an edit to the source cells):

1. Fetch the item title from Define!D11 and insert as first line of comment in Classify!K18 in bold font
2. Fetch the item description from Define!E11 and insert as second line of comment in Classify!K18 in regular font
3. Repeat for the other 9 linked cell/comment pairs (Define's D12 & E12 --> Classify's L18, etc)

Thanks,

SDL

 

[James006]

Hello,

Congratulations on your efforts and your code ...

Below is a slightly modified version to be tested ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("D11:E20")) Is Nothing Then Exit Sub
Dim i As Integer
Dim j As Integer
Dim cmt As Comment
i = Target.Row
j = Target.Column
   With Worksheets("Classify")
      ' Determine Column modified by User
      Select Case j
      'Re-compose Items Comments in Classify worksheet
          Case 4
            .Cells(18, i).Comment.Text Text:=Target.Value & Chr(10) & Target.Offset(0, 1)
          Case 5
            .Cells(18, i).Comment.Text Text:=Target.Offset(0, -1) & Chr(10) & Target.Value
      End Select
      ' Define Comment and Bold first segment
      Set cmt = .Cells(18, i).Comment
      With cmt.Shape.TextFrame
        .Characters.Font.Bold = False
        .Characters(1, InStr(1, cmt.Text, Chr(10))).Font.Bold = True
      End With
  End With
End Sub

Hope this will help