Copy Comments from Sheet to Sheet [Closed]

Report
Posts
30
Registration date
Monday March 15, 2010
Status
Member
Last seen
September 15, 2010
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
I am using this formula: =IF(ISBLANK(Master!A1),"",Master!A1)

If A1 on the 'Master' sheet has a comment, how can I bring it to the sheet I am
using this formula in?


2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Are you looking to not only show value but also comments from the cell ? So if a cell on master had a value and a comment, you want to show both ?
Posts
30
Registration date
Monday March 15, 2010
Status
Member
Last seen
September 15, 2010

I want to show both
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Ok first the bad news. If you update the comment in the original cell, the code will not update. This works for the first time only.

Function PeekThru(myLinkCell As Range, thisRow As Long, thisCol As Integer, mySheet As String) As Variant

    On Error Resume Next
        Cells(thisRow, thisCol).Comment.Delete
    On Error GoTo 0
    
    If (myLinkCell.Comment.Text <> "") Then
    
        With Sheets(mySheet).Cells(thisRow, thisCol)
            .AddComment
            .Comment.Visible = False
            .Comment.Text Text:=myLinkCell.Comment.Text
        End With
        
    End If
    
    PeekThru = myLinkCell

End Function



The closest I was able to make it more dynamic was if you add this routine to the sheet events
Private Sub Worksheet_Calculate()

    Application.EnableEvents = False
    Application.CalculateFull
    Application.EnableEvents = True
    
End Sub
Posts
30
Registration date
Monday March 15, 2010
Status
Member
Last seen
September 15, 2010

Will this work for just one cell? Or does this work for the entire sheet?

This would need to grab comments from the entire sheet. Also, since this is a function, do I need to have a button call the function? Does it even require a button?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Instead of this
=IF(ISBLANK(Master!A1),"",Master!A1)

use like this

=PeekThru(Master!A1, Row(), Column(), "Detail")

"Detail" is the sheet name where you are entering this formula. If this is some other sheet, correct the name

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!