Show partial text + expand it on click

[MMarius]

Hi,

I have like 3-4 paragraphs of text in each cell, I would like to display only the first line (keep 12.75 row height) and if i click on the cell, to expand to show the full text. Like a "read more" on the cell itself

Thank you very much.

 

[Peter_SSs]

Glad I could help.

Just noting that post 9 is not made by the member who asked the original question, nor does it identify which of the several solutions suggested that it was referring to.

 

[trunten]

Lol. Totally missed that. In that case I’m glad I may or may not have helped someone maybe!

 

[MMarius]

Well I'm surprised your standard Row Height is 12.75 that seems really small but:
Try this if you double click on any cell with some value meaning not empty and the row height is 12.75 the row height will expand to 75.

If you double click on any cell with some value and the row height is 75 the row height will be 12.75

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  7/26/2019  8:29:43 PM  EDT
If Target.Value <> "" Then
    Cancel = True
    With Rows(Target.Row)
        Select Case Rows(Target.Row).RowHeight
            Case Is = 12.75
                .RowHeight = 75
            Case Is = 75
                .RowHeight = 12.75
            End Select
    End With
End If
End Sub

Yes, works like intended, good!

Here is another option to try, triggered when you select/deselect one of the cells. It is implemented in the same way described in post 6.
I have assumed that you are wanting to implement this in a particular column and my code uses column 5 (E) as an example. If my assumption is incorrect, please give more details about where these cells with considerable text are located.

Option Explicit
Private LastAlteredRow As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If LastAlteredRow > 0 Then Rows(LastAlteredRow).RowHeight = 75
  If ActiveCell.Column = 5 Then '5 = column E, adjust this to your required column
    ActiveCell.EntireRow.AutoFit
    LastAlteredRow = ActiveCell.Row
  End If
End Sub

It works, but can it be when I click back, to go to the before value? (like 12.75?). So I set (check above with red) to 75, and it works like intended, it expands the cell, but it remains expanded.

Not sure if you were saying you had discounted the idea of a comment but you could give this a go. Again, paste into the sheet module. Shouldn't care where your oversized values are.

Private cmt As Comment


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim currentWidth As Double, currentHeight As Double
    Dim expandedWidth As Double, expandedHeight As Double
    
    On Error Resume Next
    If Not cmt Is Nothing Then cmt.Delete
    On Error GoTo 0
    
    If Target.Count > 1 Or Target(1, 1).Value = "" Then Exit Sub
    
    currentWidth = Target.ColumnWidth
    currentHeight = Target.RowHeight
    Application.ScreenUpdating = False
    Target.EntireColumn.AutoFit
    Target.EntireRow.AutoFit
    expandedWidth = Target.ColumnWidth
    expandedHeight = Target.RowHeight
    Target.ColumnWidth = currentWidth
    Target.RowHeight = currentHeight
    Application.ScreenUpdating = True
    If expandedWidth > currentWidth Or expandedHeight > currentHeight Then
        If Target.Comment Is Nothing Then
            Set cmt = Target.AddComment(Target.Value)
            cmt.Shape.TextFrame.AutoSize = True
        End If
    End If
End Sub

Yes, nice trick, works like a charm also this one.



Thank you to all 3! Hopefully it will help many others

 

[Peter_SSs]

It works, but can it be when I click back, to go to the before value? (like 12.75?). So I set (check above with red) to 75, and it works like intended, it expands the cell, but it remains expanded.

It stays expanded because you changed the 12.75 in my code to 75. The 12.75 is what changes the row back to that height. I don't understand why you made that change?

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.