For a cell in Excel overfilled with text that can't be displayed in full, what is the best way to display all text upon hovering with the mouse or selected with cursor?

For a cell in Excel overfilled with text that can't be displayed in full, what is the best way to display all text upon hovering with the mouse or selected with cursor?

For example, if I use Data/Data validation/Input message, is it possible to make this dynamic so that the message displayed is what the cell contains?

Or would I need to use VBA? For example if I could use VBA for getting each cell to autosize itself when the cell is selected or hovered over with the mouse pointer, would that be possible?

In other words, the cell's size is too small to display all the contents, and I want all contents in it to be displayed when the cell is selected with the cursor or hovered over with the mouse pointer.

I have Excel 2007.
LVL 1
hermesalphaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DougCommented:
How many characters are in these cells? Would it be sufficient to resize the formula bar (drag the bottom edge down) or just to format the cells as wrap text?
0
Roy CoxGroup Finance ManagerCommented:
a vba solution could be
Option Explicit

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Len(ActiveCell.Value) > 0 Then MsgBox ActiveCell.Value
End Sub

This is workbook event code so clicking any cell with contents will display the contents in a mesage box

Where to paste the code
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
How about adding a comment to cells with long text?  That way, the mouse hover will show the text.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DougCommented:
Here's an example of what I mean by widening the formula bar. It should work even for extraordinarily long text and it will always show the text in the current cell.  You do lose a little real estate out of the cell grid but if you're main interest is the text in the current cell then it probably doesn't matter.

Expanded formula bar.
0
regmigrantCommented:
Try this in the 'Worksheet' section
you may want to play with the width/height and the length of text to add a comment to

Each time you select a cell this will add and display a comment box

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    strcmmt = Target.Cells(1, 1).Value
    If Len(strcmmt) > 10 Then                                              ' only change those with more than 10 
        With Target
            Target.Cells(1, 1).ClearComments                       'remove previous comments
            Target.Cells(1, 1).AddComment strcmmt           'put the value is as a comment
            Target.Cells(1, 1).Comment.Shape.Width = 250  'how wide the box is (not character length)
            Target.Cells(1, 1).Comment.Shape.Height = Len(strcmmt) * 2  'how high the box is 
        End With
    End If
End Sub

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
Just a slight amendment to my previous code to ensure that multiple cells are not selected

Option Explicit

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    With Target
        If .Count > 1 Then Exit Sub
        If Len(.Value) > 0 Then MsgBox .Value
    End If
End Sub

Open in new window

0
hermesalphaAuthor Commented:
Sorry for not replying until now. This looks great, will try it out in the weekend.
0
hermesalphaAuthor Commented:
I tried below VBA code instead, and it worked fine except that the whole comment gets displayed on one single long row only. So the comment disappears in the right side of the screen.

Would it be possible to make some changes to this code below so the comment's width and height adjust automatically according to how close the selected cell is to the right margin of the screen and to the bottom margin of the screen (the closer to the right margin of the screen, the less the width would be; and the closer to the bottom margin of the screen, the less the height would be)?

This is the VBA code (which worked fine, except this detail that the comment gets displayed on a long single row only):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim str As String
On Error Resume Next
str = Target.Value
Cells.ClearComments
If Target <> "" Then
   Target.AddComment str
   Target.Comment.Visible = True
   Target.Comment.Shape.TextFrame.AutoSize = True
   On Error Resume Next
If Target.Validation.Type <> 3 Then Exit Sub
On Error GoTo 0

With Target.Validation
     .InputMessage = Target.Value
End With
End If
End Sub
0
regmigrantCommented:
Your code is essentially the same as this earlier answer

use .Comment.Shape.Width and .Comment.Shape.height to adjust the comment box as I said in my earlier answer (you can easily calculate the optimum values within the process) - Excel should handle placement to avoid going off the screen once the box size is proportionate
0
Roy CoxGroup Finance ManagerCommented:
The reason that I suggested a message box is that it will adjust itself. A comment won't so requires the code to be tweaked according to the cells contents
0
Roy CoxGroup Finance ManagerCommented:
Glad to be of assistance. Thanks for the feedback.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.