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

Pvidya

New member
Joined
May 15, 2014
Messages
4
Reaction score
0
Points
0
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
 

Attachments

  • Example.xlsx
    10.7 KB · Views: 27

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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
 

Pvidya

New member
Joined
May 15, 2014
Messages
4
Reaction score
0
Points
0
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 

Charles

Member
Joined
Feb 17, 2013
Messages
34
Reaction score
0
Points
6
Location
Biloxi
Excel Version(s)
Mac Excel 2011

Pvidya

New member
Joined
May 15, 2014
Messages
4
Reaction score
0
Points
0
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
 

Charles

Member
Joined
Feb 17, 2013
Messages
34
Reaction score
0
Points
6
Location
Biloxi
Excel Version(s)
Mac Excel 2011
Hi,

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