Copy Comments from Sheet to Sheet

Closed
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010 - May 7, 2010 at 09:49 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 7, 2010 at 12:46 PM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 7, 2010 at 10:24 AM
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 ?
0
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010
May 7, 2010 at 11:01 AM
I want to show both
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 7, 2010 at 12:08 PM
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
0
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010
May 7, 2010 at 12:21 PM
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?
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 7, 2010 at 12:46 PM
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
0