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 in Others by Kithuzzz
• 20,660 points
30 views

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
            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)
answered Nov 12 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer
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 in Others by narikkadan
• 37,660 points
20 views
0 votes
0 answers

how can I deploy a test in pytest framework to airflow using DAG?

I have established an automative testing framework ...READ MORE

Oct 4, 2021 in Others by Yuan
• 120 points
214 views
0 votes
1 answer

How can I open a URL in Android's web browser from my application?

ry this: Intent browserIntent = new Intent(Intent.ACTION_VIEW, Uri ...READ MORE

answered Jun 14 in Others by polo
• 1,480 points
971 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

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

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
452 views
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,100 points
397 views
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,100 points
3,373 views
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
2,837 views
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 in Others by narikkadan
• 37,660 points
62 views
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 in Others by narikkadan
• 37,660 points
44 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP