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!

    Hi,

    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.

    Regards

    Prayank
    Attached Files Attached Files

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

    Code:
    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.Rows(1).AutoFilter
            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
            
            rng.Rows(1).AutoFilter
        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.

    Regards

    Prayank

  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
    Location
    Biloxi
    Posts
    34
    Articles
    0
    HI,

    The user cross post this request.
    http://www.msofficeforums.com/excel-...tion-upon.html
    http://www.dbforums.com/microsoft-ex...alue-cell.html

    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.
    http://www.msofficeforums.com/excel-...tion-upon.html

  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.

    Regards

    Prayank

  8. #8
    Acolyte Charles's Avatar
    Join Date
    Feb 2013
    Location
    Biloxi
    Posts
    34
    Articles
    0
    Hi,

    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
  •