How to wrap comment.text in vba

Question

I have a situation in which text in one of the cells is massive.When I change the contents of this cell the previous value becomes a comment to that cell.Now the problem is, this text is so big that I cannot see complete text.If I use .Shape.Textframe.Autosize=true then I have to go on browsing till god knows when to see the text.

What I need to do is whatever and however big the text might be in Commnet.text,I want to show it in one and one place only.i.e when I hover on comment.

Answer 1

How about a pop-up text box? I.e., the user clicks on the cell and a pop-up form with the text appears. The best part is you can make the pop-up as big as you want.

Personally, I would make the pop-up resizable, but with only the Close button at top. I can't think of a way to trigger this form with just a mouse rollover, but I'm only using 2003. Later versions allow you more tricks.

Answer 2

Place a TextBox where you will on your worksheet adjusted to the size you want, this still reads the comment although this is not on a hover, but perhaps will help

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Not ActiveCell.Comment Is Nothing Then

   ActiveSheet.Shapes("Text Box 1").TextFrame.Characters.Text = ActiveCell.Comment.Text
  Else: ActiveSheet.Shapes("Text Box 1").TextFrame.Characters.Text = ""
 End If
End Sub

Actually after considering this further you really are trying to get comment to act as you want. So I recommend Resize all comments in the selected area and on the same link further down the page Show Excel Comments in Centre of Active Window.

These get you closer to what i think you want.