Visualize comments as plain text in other cell

[edge37]

Good day, I was wondering is there is a way to visualize a comment of a cell in another cell as plain full text (not as comment as well). I want to input comments on some student's grades and when I click on that cell I want the student see the comments in another cell, just like the representation I made in the pic. Thanks for your help

 

[arthurbr]

Perhaps there is something suitable at Excel Comments Macros Examples Resize Print Move Pictures

 

[edge37]

Perhaps there is something suitable at Excel Comments Macros Examples Resize Print Move Pictures

Thanks for your answer, mate, unfortunately none of those solutions are exactly what I am looking for, but I'm going to use some of those macros as well.

 

[Peter_SSs]

See if this worksheet selection change event code does what you want.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    If Not Target.Comment Is Nothing Then Range("B7").Value = Mid(Target.Comment.Text, InStr(1, Target.Comment.Text, ":") + 1)
  End If
End Sub

 

[edge37]

See if this worksheet selection change event code does what you want.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    If Not Target.Comment Is Nothing Then Range("B7").Value = Mid(Target.Comment.Text, InStr(1, Target.Comment.Text, ":") + 1)
  End If
End Sub

Thank you Peter, it works when I click the cell with comment, but the comment will stay there even if I click some other cell. My idea is that when this happens, I click other cell with another comment and now the new comment shows, or if I click any other cell without a comment nothing appears in the cell. Maybe an extra VB code would nail it. Thanks anyway for your help.

 

[ACommandLineKindaGuy]

I see, so if B7:E8 is the range that has the borders around it, you want that range to be dynamic, meaning showing what the active cells comment says--if it has a comment.

Peter's code just needs a couple of else statements

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.CountLarge = 1 Then
      If Not Target.Comment Is Nothing Then
           Range("B7").Value = Mid(Target.Comment.Text, InStr(1, Target.Comment.Text, ":") + 1)
      Else
           Range("B7").ClearContents
      End If
   Else
          Range("B7").ClearContents
   End If
End Sub

However, I'm not sure why you need to test if the target has an object (If Target.CountLarge = 1), it seems that's just an unnecessary extra step

 

[Peter_SSs]

Peter's code just needs a couple of else statements

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge = 1 Then
If Not Target.Comment Is Nothing Then
Range("B7").Value = Mid(Target.Comment.Text, InStr(1, Target.Comment.Text, ":") + 1)
Else
Range("B7").ClearContents
End If
Else
Range("B7").ClearContents
End If
End Sub

I believe that code will error given the OP's set-up where the comment display area appears to be a group of merged cells.

I'm not sure why you need ... (If Target.CountLarge = 1), it seems that's just an unnecessary extra step

That's a fair enough comment. My reasoning was that since the comment would be in a single cell** it seemed reasonable to avoid the rest of the code if we were not dealing with such a scenario.

** However, your comment prompted me to re-consider the code and I guess it might be possible that a comment itself may be in a merged cell area and if so, my previous code would not display it.
@edge37 If it is possible that you have actual comments in merged cells then the code below will also need modification as for now I have still assumed they are in single cells

This would be my revised code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Comment Is Nothing Then
    Range("B7").Value = ""
  Else
    Range("B7").Value = Mid(Target.Comment.Text, InStr(1, Target.Comment.Text, ":") + 1)
  End If
End Sub