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 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 7, 2010 at 12:46 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 7, 2010 at 12:46 PM
Related:
- Copy Comments from Sheet to Sheet
- Google sheet right to left - Guide
- Windows network commands cheat sheet - Guide
- Mark sheet in excel - Guide
- Little alchemy cheat sheet - Guide
- How to open excel sheet in notepad++ - Guide
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 7, 2010 at 10:24 AM
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 ?
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 7, 2010 at 12:08 PM
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.
The closest I was able to make it more dynamic was if you add this routine to the sheet events
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
DCecil07
Posts
30
Registration date
Monday March 15, 2010
Status
Member
Last seen
September 15, 2010
May 7, 2010 at 12:21 PM
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?
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?
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 7, 2010 at 12:46 PM
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
=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
May 7, 2010 at 11:01 AM