VBA code. Pivot table drilldown on a new windows form

hcova

New Member
Joined
Jul 29, 2010
Messages
4
Hi There.
If you double click on a pivot table cell, a new sheet automatically opens and displays the database list. Then you need to delete it manually and go back to your pivot table.
What I really would like to do is create an Excel VBA Code so when you double click in the cell, the database list will be displayed in a new floating window instead of the default new sheet.
This new floating window should keep open until the user decides to close it.
The user needs to keep open many floating windows at the same time, according to the number of cells the user has double-clicked on the pivot table.
Let me show this with images:
1) This is the current pivot table

1611451872971.png


2) Then the user makes a double click in B15 Cell and a pop-up appears displaying the database.
Next, the user makes a double click in another cell (C17) and a new pop-up appears in the database instead of a new sheet.
A design detail: The double-clicked cells change their background colors as well as the pop-up window borders.

1611452111668.png


Any code, help, or link will be welcomed
Best Regards
Hernán
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
391
Office Version
  1. 2016
Platform
  1. Windows
Hello Hcova,
here is one quick created code.
It's not ideal but you can use it as basic template.
Code have dangerous lines to create and remove folder and files, so be careful when testing it. Test it with debuger line by line.
Every time you click on the pivot table code create new folder in the "E:\"PivotTable\" location
and ".xlsx" file (from pivot table data sheet) .
Imideatly after that opens this new file as pop up Excel window in the specific location.
When main workbook is closed temporary folder "E:\"PivotTable\" will be delated.
Window border color will not be changed, but sheet tab changing color.
All this code goes to the "ThisWorkbook" module...

VBA Code:
Option Explicit

Dim vSWBDC As Boolean
Dim vT As Long, vL As Long
Dim vRed As Integer, vGreen As Integer, vBlue As Integer
Dim vWB As Workbook
Dim vWBCaption As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    On Error Resume Next
    Kill "E:\PivotTables\*.*"    ' delete all files in the folder
    RmDir "E:\PivotTables\"      ' delete folder
    On Error GoTo 0
    
End Sub

Private Sub Workbook_Open()

    On Error Resume Next
    MkDir "E:\PivotTables"       'create temporary folder
    
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    If vSWBDC = True Then
        Application.ScreenUpdating = False
        ActiveWindow.WindowState = xlNormal
        ActiveWindow.Top = 0
        ActiveWindow.Left = 0
        ActiveWindow.Width = 1000
        ActiveWindow.Height = 700
        vWBCaption = ActiveWindow.Caption
        Sh.Copy
        ActiveWorkbook.ActiveSheet.Tab.Color = RGB(vRed, vGreen, vBlue)
        With ActiveWorkbook
            .SaveAs Filename:="E:\PivotTables\" & Sh.Name, _
                  FileFormat:=xlOpenXMLWorkbook
            .Close False
        End With
        vSWBDC = False
        Workbooks.Open "E:\PivotTables\" & Sh.Name
        
        Application.DisplayAlerts = False
        Sh.Delete
        vT = 0
        vL = 500
        For Each vWB In Workbooks
            With Workbooks(vWB.Name)
                If Not vWB.Name = vWBCaption Then
                    .Activate
                    .Windows(vWB.Name).Height = 300
                    .Windows(vWB.Name).Width = 300
                     vL = vL + 20
                     vT = vT + 30
                    .Windows(vWB.Name).Left = vL
                    .Windows(vWB.Name).Top = vT
                End If
            End With
        Next vWB
        Application.ScreenUpdating = True
    End If
    
End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    
    If Sh.Name = "PivotTable" And Not Target = "" Then
        vRed = Int(255 * Rnd)
        vGreen = Int(255 * Rnd)
        vBlue = Int(255 * Rnd)
        Target.Interior.Color = RGB(vRed, vGreen, vBlue)
        vSWBDC = True
    End If

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,314
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top