mouseover or click-on event to show cell contents in a display message box

[piers_morgan]

Hi gurus,

The issue I have is a row of complicated values in row 2. I need to explain/provide clalrifications about these values by way of a mouse-over event (preferably) but clicking that cell will do fine too. So if I go for the easier click option, clicking on a cell in row 2 would trigger a pop-up box to show my explanation that I have for that cell in row 1.

In more detail:

Click on cell A2 would trigger pop up box that would display text that I have in cell A1. Clicking on cell B2 would show text from cell B1 etc.

I could use a comment box, but that's cumbersome for me in that I have more than 50 cells that will need comment boxes and I will need to update these on a regular basis. As I will have 20 or so with the same explanations in row 1, it's far easier for me to update these cells in row 1 rather than update each cell comment 1 by 1...50x....and then across 20sheets!


Thanks in advance, P

 

[Jeffrey Mahoney]

This code adds a comment to the cell below the cell you put the text. I created a named range on my sheet called 'TextRange'. The range is A1:J1. When I put text in any of these cells, the macro will add that text as a comment to the cell below. Edit A1 and A2 will get the new comment. You can also copy text to these cells and it will do the same thing.

You need to put this code in a SHEET module. Whatever sheet you are working on.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim Cel As Range
  Dim cc As Range
  
  Set i = Intersect(Target, Range("TextRange"))
  If Not i Is Nothing Then
    For Each Cel In i
      Set cc = Cel.Offset(1, 0)
      cc.ClearComments
      cc.AddComment
      cc.Comment.Text Text:=Cel.Value
    Next Cel
  End If
  
End Sub

 

[Jeffrey Mahoney]

Did you want the comment added to all non-empty cells in each column?

 

[piers_morgan]

Hi Jeffrey,

Thanks very much. I can work with this and has saved me a few hours for sure! The only remaining issue I have is the comment boxes themselves. For example if my original text is only a few words and then I later go in and update the text to a small paragraph, then the comment box does not re-adjust / resize accordingly. Is there a way to do that? Your solution is neat, but if I am then having to go in and adjust the sizes of all the text boxes....

Thanks,

P

 

[Jeffrey Mahoney]

I did a little homework and played with some code. This is all I could come up with. For some reason the Autosize makes the comment box one line tall. Maybe you can take it further.

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim i As Range
  Dim Cel As Range
  Dim cc As Range
  
  Set i = Intersect(Target, Range("TextRange"))
  If Not i Is Nothing Then
    For Each Cel In i
      Set cc = Cel.Offset(1, 0)
      cc.ClearComments
      cc.AddComment
      cc.Comment.Text Text:=Cel.Value
      cc.Select
      With cc.Comment
        .Shape.ScaleWidth 2, msoFalse
        Select Case Len(Cel.Value)
          Case Is > 400
            .Shape.ScaleHeight 2.25, msoFalse
          Case Is > 300
            .Shape.ScaleHeight 2#, msoFalse
          Case Is > 200
            .Shape.ScaleHeight 1.5, msoFalse
          Case Is > 100
            .Shape.ScaleHeight 1.25, msoFalse
        End Select
          
        '.Shape.TextFrame.AutoSize = True
    End With
    Next Cel
  End If
  
End Sub

 

[piers_morgan]

Hi Jeffrey, Thanks very much for that. I made your other option work very nicely in the end, but when I get to my other laptop will try this latest code.

Really appreciate that!! : )