Can I show a formula result in a pop-up window in Microsoft Excel

Question

Using the fundamental =VLOOKUP() formula, I am creating an Excel spreadsheet with a set of merged cells that searches for an object and returns a description of that item (for example, selecting "apple" in the target cell would return something like "A red fruit" in the cell group). However, the description is frequently so lengthy that even with text wrapping, it is still terminated at the conclusion. Given that the region would need to be at least 12 rows tall and that I only have a limited amount of space in that portion to deal with, I don't want to simply increase the size of the cells or combine more cells into the group.

So my question is this: Is it possible to create a pop-up window (similar to a comment or data validation pop-up) that shows the full text of the formula result when the cell is selected?

Answer 1

Yes, you can display the formula result—that is, the cell's value—in the comment of a chosen cell.

The value of each chosen cell is displayed as a remark if you include this code into your worksheet's code module. The preceding comment gets removed if you choose a different cell. Be mindful that this code will remove any further regular comments you add.

You can restrict this functionality, for instance, to a worksheet's predefined range or to cells with formulas.

Option Explicit

Private cmt As Comment

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim MonitoredCells As Range
    Dim c As Range

    ' Comments are shown as red triangle, all hidden by default:
    Application.DisplayCommentIndicator = xlCommentIndicatorOnly

    ' Delete previous comment, if any
    If Not cmt Is Nothing Then cmt.Delete

    ' limit this functionality to e. g. columns A:C by this:
    Set MonitoredCells = Intersect(Target, Target.Parent.Range("A:C"))
    If Not MonitoredCells Is Nothing Then
        Set c = MonitoredCells(1) ' Do it for the first selected cell only
        ' limit this functionality to cells with formulas:
        If c.HasFormula Then
            If c.Comment Is Nothing Then
                Set cmt = c.AddComment
            Else
                Set cmt = c.Comment
            End If
            cmt.Text Text:=CStr(c.Value)
            c.Comment.Visible = True
        End If
        Set c = Nothing
    End If
End Sub

If the cell's formula contains CHAR(10) line feeds, then try to autosize the comment by this:

cmt.Shape.TextFrame.AutoSize = True

Autosizing is useless if the autosized comment is simply one line. If so, you can increase its size by applying the formula before displaying it. The size is 100.8 x 72 points by default. The text that follows doubles the width and increases the height roughly to the number of characters required, or at :

cmt.Shape.Width = 200
cmt.Shape.Height = WorksheetFunction.Max(72, 0.35 * cmt.Shape.TextFrame.Characters.Count)