Results 1 to 8 of 8

Thread: Help with a pop up window with relevant information upon clicking a value in cell

  1. #1

    Help with a pop up window with relevant information upon clicking a value in cell

    Register for a FREE account, and/
    or Log in to avoid these ads!


    I have a dashboard with 3 columns as depicted below, where year means the period referred to, Newbees are the new agencies and Existing are the already existing agencies.

    Year Newbee Existing
    2010-11 2.12 163.43
    2011-12 7.92 206.04
    2012-13 11.83 236.19
    2013-14 16.95 237.90

    What I want to do is that when I click on cell A2 (2010-11) or cell A3 (2011-12) or so on, it opens a pop up or a window and displays the names of the agencies in the popup as displayed below:

    2010-11 (Period referring to)

    Sl. No. Newbee Existing

    1 Amit assoc. Gupta & co
    2 Parth co. Aggarwal Logistics
    3 Glennfier The Company
    4 Bumbble ABC & Co
    5 XYZ assoc
    6 hfuh pvt ltd
    7 Mhfnd & co.

    This information will be pulled form a source sheet in the same workbook which will have the period wise breakup of the Newbees and Existing agencies.
    I have attached an example file as well.
    Please help as this is really urgent and with your help I might be able to close it and share this dashboard with my COO today itself.


    Attached Files Attached Files

  2. #2
    Add this code to the Dashboard code module

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)Dim rng As Range
    Dim rngrow As Range
    Dim msg As String
    Dim i As Long
        If Not Intersect(Target, Me.Range("A1").CurrentRegion.Columns(1)) Is Nothing Then
            Set rng = Worksheets("Source").Range("A1").CurrentRegion
            rng.AutoFilter Field:=2, Criteria1:=Target.Value
            For Each rngrow In rng.SpecialCells(xlCellTypeVisible).Rows
                msg = msg & rngrow.Cells(1, 1).Value & vbTab
                msg = msg & rngrow.Cells(1, 2).Value & vbTab
                msg = msg & rngrow.Cells(1, 3).Value & vbTab & vbTab
                msg = msg & rngrow.Cells(1, 4).Value & vbNewLine
            Next rngrow
        End If
        MsgBox msg
    End Sub

  3. #3
    Hi Bob!

    Thanks for your prompt response. It works. Could you also help in fixing the formatting as the way window renders, the text gets distorted and unstructured.



  4. #4
    Unfortunately, because it is not a proportional font that is very hard, not something I want to get into. I suppose you could popup a userform with a listbox with 4 columns.

  5. #5
    Thanks Bob! I will try that.

  6. #6
    Acolyte Charles's Avatar
    Join Date
    Feb 2013

    The user cross post this request.

    No telling where else.
    I proved an answer, and waited for a response...But!!!! A waste of time on my and other who looked.
    This is wher I tried to help.

  7. #7
    Hi Charles!

    Thank you so much for taking time out and helping me with this. This works really but what I wanted is that the information opens at the same place where the person is clicking to see the breakdown for the viewer's ease. That is why I wanted this information to open in either a pop up, a list box or a small window and when the information gets populated in the window, it is well staked and not distorted.



  8. #8
    Acolyte Charles's Avatar
    Join Date
    Feb 2013

    Thanks for the response.
    My thought was if you had several rows of data it may over whelm the "MsgBox.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts