show contents of cell when hovering

[mikeha_99]

Hello,
I have a cell with lengthy text in it, and I do not want to change the height and width of the cell. I would like all the text to be visible when I hover the mouse over the cell.

I do not want to have to select the cell, or view the cell contents in the formula bar, or insert a comment. Just view all text when hovering over the cell.

Is this possible? If so, please indicate how I would do this.

Thanks,
Mike

 

[mikeha_99]

Thanks everyone for all the advice. I understand the exact method I envision isn't an option. I am looking at working with some of the other suggestions. I certainly appreciate all the input.

Mike

 

[mikeha_99]

I have been experimenting with the code submitted by ChrisOswald.

This is very helpful. I get exactly what I need. However, I would like to customize it to a single column. At present, I think any cell with data gets the comment added.

So....I will try to adjust the code myself, so it only works on a single column. If anyone can suggest how to do this, i.e. only add comments to column D for example, that would be great.

Overall, this is an excellent solution for my problem. I really appreciate it.


Mike

 

[taigovinda]

Both of his subs have this line in it, I believe if you add the part in red then it will work on just column D:

        For Each iCell In intersect(WS.UsedRange,ws.range("D:D"))

 

[mikeha_99]

Hi,
Thanks for the code. I have tried it, and it works, but I get an error. When I click on Debug, I see this line

For Each iCell In intersect(WS.UsedRange,ws.range("D:D"))

Is highlighted in yellow

The message box says:
Run-time error "424':
Object required

The comments are indeed placed into the cells in column D, but I get an error.


Additionally, is there any way to make to comment pop-out to the left? At present, it is out of view, because it opens to the right.

 

[mikeha_99]

Select the cell and set an accelerator that calls a macro to return a message box with the contents of the active cell?

I am still interested in a better solution for this question. The idea of a message box opening up showing the contents of the cell appeals to me. How would this be done?

Mike

 

[cecilmac]

Here's my 2 cents.

The display format of comments seems to be exactly what you want. However, you're worried that you aren't going to be able to Ctl-F to quickly locate cells. You can use code to add comments such that they have either the value of the cell or the formula in the cell. The first one is currently set to show the formula if a cell has one; otherwise it'll show the cells text.

Sub CommentTheHeckOuttaIt()
    Dim WS                          As Worksheet
    Dim iCell                       As Range
    For Each WS In ActiveWorkbook.Worksheets
        For Each iCell In WS.UsedRange
            With iCell
                If CStr(.Value) <> "" Then
                    .ClearComments
                    .AddComment
                    .Comment.Visible = False
                    .Comment.Text Text:=CStr(.Value)
                    .Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
                    .Comment.Shape.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft
                End If
                If .Formula <> "" Then
                    .ClearComments
                    .AddComment
                    .Comment.Visible = False
                    .Comment.Text Text:=CStr(.Formula)
                    .Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
                    .Comment.Shape.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft
                End If
            End With
        Next
    Next
End Sub
 
 
 
Sub AllCommentsMustDIE()
    Dim WS                          As Worksheet
    Dim iCell                       As Range
    For Each WS In ActiveWorkbook.Worksheets
        For Each iCell In WS.UsedRange
            iCell.ClearComments
        Next
    Next
End Sub

For demo purposes, you could run the first macro. when finished with the demo, you run the second macro.
Try them out on a copy of your workbook.

Hi Chris - This code is just what I was looking for - excellent - just one thing, how would you format the text in the Comment Box?

Cheers
cecilmac

 

[AmandaGomes]

Here's my 2 cents.

The display format of comments seems to be exactly what you want. However, you're worried that you aren't going to be able to Ctl-F to quickly locate cells. You can use code to add comments such that they have either the value of the cell or the formula in the cell. The first one is currently set to show the formula if a cell has one; otherwise it'll show the cells text.

Sub CommentTheHeckOuttaIt()
    Dim WS                          As Worksheet
    Dim iCell                       As Range
    For Each WS In ActiveWorkbook.Worksheets
        For Each iCell In WS.UsedRange
            With iCell
                If CStr(.Value) <> "" Then
                    .ClearComments
                    .AddComment
                    .Comment.Visible = False
                    .Comment.Text Text:=CStr(.Value)
                    .Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
                    .Comment.Shape.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft
                End If
                If .Formula <> "" Then
                    .ClearComments
                    .AddComment
                    .Comment.Visible = False
                    .Comment.Text Text:=CStr(.Formula)
                    .Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
                    .Comment.Shape.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft
                End If
            End With
        Next
    Next
End Sub
 
 
 
Sub AllCommentsMustDIE()
    Dim WS                          As Worksheet
    Dim iCell                       As Range
    For Each WS In ActiveWorkbook.Worksheets
        For Each iCell In WS.UsedRange
            iCell.ClearComments
        Next
    Next
End Sub

For demo purposes, you could run the first macro. when finished with the demo, you run the second macro.
Try them out on a copy of your workbook.

Eleven years later, and I'm here, using your code! You just saved me! Thank you so much!