Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Link to cell with comments (Excel 2003)

    I have a worksheet with lots of linked cells. Some of the source cells have comments. I cannot see the comments in the target cells. Is there a way to see those comments - other than clicking on the source cell? I want to hide the sheets with the source data, which would keep other uses from seeing the comments.

    Thanks in advance,

    itconc

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Link to cell with comments (Excel 2003)

    I thought the dependents (as well as precedents) property only finds dependencies on the same sheet. How do you find them on different sheets?

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link to cell with comments (Excel 2003)

    Oops! You're correct (of course). Thanks for pointing out my mistake.

    So the code would have to loop through the cells in the target sheet(s) and parse the formulas. Rather tedious!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link to cell with comments (Excel 2003)

    Added: see Steve's reply below. My suggestion won't work!

    You could write code that loops through all cells in the source sheet with comments, and uses the Dependents property to find the cells that refer to the source cell. You can then set the comment for the referring cell.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Link to cell with comments (Excel 2003)

    Thanks, I was looking for a non VBA way. I guess there is none.

    itconc

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Link to cell with comments (Excel 2003)

    I imagined you would only do it in the target if the "formula" refered to only 1 cell and then get that cells comment. It would make the "parsing" a little easier but it would still be tedious to code it. [I had thought about using INDIRECT ins ome way, but that is not available to VBA]

    Steve

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Link to cell with comments (Excel 2003)

    Hi itconc,

    For a vba solution you could use something like:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells(1, 1).HasFormula = True And Flag = 0 Then
    Flag = 1
    ShowLinkedComment
    End If
    End Sub

    in the target worksheet's object and

    Public Flag As Integer
    Sub ShowLinkedComment()
    Dim RefCell As String
    Dim CoTxt As String
    On Error Resume Next
    With ActiveCell
    RefCell = Replace(.Formula, "=", "")
    CoTxt = Range(RefCell).Comment.Text
    If Len(CoTxt) > 0 Then MsgBox (RefCell & " comment:" & vbCrLf & CoTxt)
    End With
    Flag = 0
    End Sub
    [/code]
    in a general module.

    This will automatically display a messge box with the comments from the source cell referenced by your target cell - the comments are not added to the target cell. As written, the code only works for a straight =A1 or =Sheet1!A1 type of reference - it even works with references to external workbooks if they're open.

    Nothing will be displayed if the source cell has no comment, or the formula referencing it has any operators (eg + - / * & < = >) other than the leading '=' or is embedded in other functions (eg =Sheet1!A1 works, but =CELL("contents",Sheet1!A1), which would display the same value, doesn't - this too might be useful if you don't want certain comments to show).

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link to cell with comments (Excel 2003)

    Clever code, but MsgBox seems a very clunky way to display comments, especially if there are many source cells with comments...

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Link to cell with comments (Excel 2003)

    Hi Hans,

    Yes, the message box is a bit clunky, but I didn't want to add the comments to the selected cell (even temporarily) as I'd then have to code for the possibility that the user might want to add their own comments to those cells. I suppose I could have used the status bar instead, but that's less likely to be noticed.

    Here's some code to temporarily display the comments from the source cell as comments in the target cell:

    In the target worksheet object:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells(1, 1).HasFormula = True And Flag = 0 Then
    ActiveSheet.Range(LastCell).Comment.Delete
    Flag = 1
    ShowLinkedComment
    Else
    ActiveSheet.Range(LastCell).Comment.Delete
    End If
    End Sub

    In a general module:
    Public Flag As Integer
    Public LastCell As String
    Sub ShowLinkedComment()
    Dim RefCell As String
    Dim CoTxt As String
    On Error Resume Next
    With ActiveCell
    RefCell = Replace(.Formula, "=", "")
    CoTxt = Range(RefCell).Comment.Text
    If Len(CoTxt) > 0 Then
    LastCell = .Address
    .AddComment
    .Comment.Text Text:=CoTxt
    .Comment.Visible = True
    End If
    End With
    Flag = 0
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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