Show partial text + expand it on click

MMarius

Board Regular
Joined
Sep 15, 2015
Messages
60
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.
 
Upvote 0
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
Code:
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
 
Upvote 0
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.

Rich (BB code):
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
 
Last edited:
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,179
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top