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.

 

[My Aswer Is This]

You could add a comment to the cell.
Right click cell and choose Comment

 

[MMarius]

Hi,

Yes, but i would like to be like a button or something like that, to expand the row.

 

[My Aswer Is This]

Expand the cell?
Can you not format the cell to alignment Auto fit

 

[MMarius]

Hello,

Autofit will not solve my issue.

I would like into a cell, something to expand. Vba maybe when i click it, to do the row height 75. I don't know.

 

[My Aswer Is This]

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

 

[Peter_SSs]

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 = 12.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

 

[trunten]

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

 

[Lukhanyo]

great solution I used it and it worked like magic. thanks!

 

[trunten]

Glad I could help.

Just thought I’d mention, my code isn’t perfect as it will also create a comment if the text fits but other cells in the row/column overflow. However, it doesn’t really harm much to have a comment pop up if not needed as it will only be temporary