CODEWITHSUNDEEP
excelvba
caaax
caaax

Reputation: 460

How to view full contents if text is too large for the cell?

I need to view the full contents of a cell in Excel.

Consider the following example:

Example

The text is too large for the cell, so I want to click/hover over the cell and view it's full contents. I initially used the Selection.Validation method to display an input message as shown above. However, the limit is 255 chars for this meaning I get an error on cells with larger contents (I also researched a way for making this bigger but you cannot).

I want the cell and text size to remain the same.

Upvotes: 0

Views: 1203

Answers (2)

Storax
Storax

Reputation: 12167

You could use the selection change event to display a shape with the cell value. Based on code from here

    Private Sub worksheet_selectionchange(ByVal Target As Range)

    Const ZOOM_CELLS = "zoomCells"

        ' Range where the cell is "zoomed"
        Dim rg As Range
        Set rg = Range("D1:D4")

        Dim zoomShape As Variant

        ' Delete zoom shapes
        For Each zoomShape In ActiveSheet.Pictures
            If zoomShape.Name = ZOOM_CELLS Then
                zoomShape.Delete
            End If
        Next

        ' Zoom only for defined range
        If Intersect(rg, Target) Is Nothing Then Exit Sub

        ' Zoom only in case one cell is selected
        If Target.CountLarge > 1 Then Exit Sub

        ' no zoom if cell is empty
        If Len(Target.Value) = 0 Then Exit Sub

        Application.ScreenUpdating = False

        Dim oldHeight As Double, oldWraptext As Boolean

        With Target
            oldHeight = .RowHeight
            oldWraptext = .WrapText

            ' increase cell height and wrap text
            .WrapText = True
            .EntireRow.AutoFit

            ' paste it as picture on the sheet
            .CopyPicture xlScreen, xlPicture

            ' restore old row height and wrapt text
            .RowHeight = oldHeight
            .WrapText = oldWraptext
        End With

        Application.ActiveSheet.Pictures.Paste.Select

        ' make pasted picture pretty
        With Selection
            .Name = ZOOM_CELLS
            With .ShapeRange
                .ScaleWidth 1.5, msoFalse, msoScaleFromTopLeft
                .ScaleHeight 1.5, msoFalse, msoScaleFromTopLeft
                With .Fill
                    .ForeColor.SchemeColor = 44
                    .Visible = msoTrue
                    .Solid
                    .Transparency = 0
                End With
            End With
        End With

        Target.Select
        Application.ScreenUpdating = True

    End Sub

Upvotes: 2

Steffen
Steffen

Reputation: 121

You could use the

ActiveCell.AddComment (ActiveCell.Text)

function of your Selection, then resize and reposition it correctly. After changing the selection delete the old comment and create a new. Also you would have to make sure that comments are always visible.

Upvotes: 1

Related Questions