How to clean up overflow text.

[absolum101]

I'm slightly unsure of how to pose my question. Basically at work I work with a log that has hundreds of entries. Each cell in the row has no overflow, save the last one called "comments". My goal is to make it so the comment cell isn't stretched to the point it is off screen, but also not taller by using "wrap text". In a since, make it uniform with the rest, but also be able to scroll to see all contents of the cell; i.e. scrollable. I found some info stating to add in ActiveX text boxes, but with the amount of entries done daily, and already on the sheet, that seemed far too tedious.

So I guess my question is, is there a way to have a cell have its overflow text view-able while not making the cell taller or shorter? (for an entire column)

 

[sykes]

Couple of things here:
1. I think your "Comments" cells are "Stretched..." because the cells to the right don't contain any data; Excel doesn't overflow text if there's data in the cell to the right of the cell in question - which is why all your other cells don't "Stretch."
2. Easiest way to solve this (if you can) is to reduce the width of all the other columns - so they're only just keeping the text contained - then expanding the "Comments" column until all text is visible. In case you don't know, to do this, drag the column boundaries right at the top - between the column letters.
3. Next - if that's not sufficient - View the cell contents by selecting the cell, then reading the contents in the "Formula Bar" just above the column letters. If there's too much text even for that, you can make the bar deeper, by use of the drop down to the very right hand side of it.
4. Lastly, you could have a simple few lines of code, to put the text from each cell, into a cell "Note" (used to be called a "Comment" ironically) so that all the user has to to is hover over the cell, and the cell contents are displayed in the note.

Sorry if I'm telling you things you know already, but I've no idea of your experience level, and sometimes the most simple solutions work the best!

 

[absolum101]

Thanks for the response. Yes, I was at step 3, as I needed to have all onscreen without scrolling and hated the random row height changes. After looking into step 4 I came up with this:

Option Explicit


Sub ConvertToComment()
Dim ws As Worksheet
Dim i As Long
Dim Comments As String


Set ws = ThisWorkbook.Sheets("Completed")
Application.ScreenUpdating = False


With ws
    For i = 2 To 1649 'Add last row function later
        Comments = Cells(i, 14).Value
        Cells(i, 14).ClearComments
        
        If Len(Comments) > 0 Then
            Cells(i, 14).AddComment
            Cells(i, 14).Comment.Text Comments
        End If
        
        Cells(i, 14).ClearContents
        Cells(i, 15).Value = "done"
    Next i
End With


Application.ScreenUpdating = True


End Sub

 

[sykes]

Nice one. Has it satisfied your requirement?

 

[absolum101]

Yes, although slightly different from how I was thinking, but I think this actually turned out better. I never really use comments(notes).

 

[sykes]

Jolly good - glad it worked for you.
With a bit more code, you can easily change the font/size etc, too.