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

0 votes
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?
Nov 12, 2022 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

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
                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)
answered Nov 12, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

How can I store the data of an open excel workbook in a collection using BluePrism?

To do what you want is like ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

How can I perform a reverse string search in Excel without using VBA?

This one is tested and does work ...READ MORE

answered Jan 5, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,090 points
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,090 points
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

How can I format bytes a cell in Excel as KB, MB, GB etc?

In Excel's formatting features, computations are not ...READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 63,700 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP