Contextual PivotTable Userform

Sometimes when I write code, I feel as if I’m writing on a cloud with a unicorn’s horn dipped in angel tears. And sometimes I feel as if I’m using a sledge hammer. This is the latter.

There have been some PivotTable shortcuts posted about (here, here, and here). When I get too many shortcuts, I’m inclined to put them on a userform (like I did with cell formatting). To that end, I want a userform to appear whenever the Pivot Table Field List task pane appears, that is, whenever the activecell is within the boundaries of a PivotTable.

I start with a class module to capture three events. I created a class called CAppEvents that looks like this:

Public WithEvents appEvents As Application

Private Sub appEvents_SheetActivate(ByVal Sh As Object)
   
    ShowHideForm Sh, ActiveCell
   
End Sub

Private Sub appEvents_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   
    ShowHideForm Sh, Target
   
End Sub

Private Sub appEvents_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
   
    ShowHideForm ActiveSheet, ActiveCell
   
End Sub

Private Sub ShowHideForm(Sh As Object, Target As Range)

    Dim pt As PivotTable
   
    ‘Start by hiding the userform
   On Error Resume Next
        gufPivotTable.Hide
    On Error GoTo 0
   
    ‘if the cell is within a pivot table on the sheet, show the userform
   For Each pt In Sh.PivotTables
        If Not Intersect(Target, pt.TableRange2) Is Nothing Then
            On Error Resume Next
                Unload gufPivotTable
                Set gufPivotTable = Nothing
                Set gufPivotTable = New UPivotTable
            On Error GoTo 0
           
            gufPivotTable.Left = pt.TableRange2.Left + pt.TableRange2.Width + 50
            gufPivotTable.Top = pt.TableRange2.Top + 50
            gufPivotTable.Show
            Exit For
        End If
    Next pt
   
End Sub

When the cell selection is changed, a different worksheet is selected, or a different workbook is activated, the ShowHideForm procedure is called. It looks at all the PivotTables on the sheet and sees if the ActiveCell is inside one of them. If it is, it shows a userform.

Globally scoped userforms can be tricky. Just because you close a userform, doesn’t mean that global variable is Nothing and you get an automation error. Now for the sledge hammer part: I hide the userform, destroy it, and recreate it every time. It avoids the error, but isn’t exactly pretty. I need to work on retaining its position too, as now it simply shows it in the same spot.

The userform has two special properties set. I set ShowModal to False so that code execution continues and so that the user can work with the PivotTable while the userform is visible. And since I have multiple monitors, I set StartUpPosition to 3 – Windows Default. With a name like “Windows Default”, you’d think it would be the default in Excel, but it’s not so.

To make the magic happen, I have a module called MEntryPoints that holds the global userform variable, the global class variable, and the code to set it all up.

Public gclsAppEvents As CAppEvents
Public gufPivotTable As UPivotTable

Sub Auto_Open()
   
    Set gclsAppEvents = New CAppEvents
    Set gclsAppEvents.appEvents = Application
   
End Sub

Sub Auto_Close()
   
    Set gclsAppEvents = Nothing
   
End Sub

It seems to work reasonably well. I’m sure I’m missing an event that has to do with the ActiveWindow, but I’m not too worried about that. I really detest using the SelectionChange event. It’s constantly firing even when I’m not using a PivotTable. When I find myself using SelectChange, I usually abandon the code and trigger it manually, which I may end up doing here.

Then there’s the issue of what to put on the userform. I initially want three things. I want some pivot field formatting options. I want to toggle between Sum and Count and maybe a couple of others. I want to group by Years and Months and Years, as those are the two I group by the most. Anything else?

Posted in Uncategorized

6 thoughts on “Contextual PivotTable Userform

  1. Dick, You might want to hide the form and not have it pop up next time you click back into a pivot table, so I tied subsequent unhidings to this module:

    Sub MakePivotFormVisible()
    Dim pt As Excel.PivotTable

    If gufPivotTable Is Nothing Then
        Set gufPivotTable = New UPivotTable
    End If
    With gufPivotTable
        For Each pt In ActiveSheet.PivotTables
            If Not Intersect(ActiveCell, pt.TableRange2) Is Nothing Then
                gufPivotTable.lngLeft = pt.TableRange2.Left + pt.TableRange2.Width + 50
                gufPivotTable.lngTop = pt.TableRange2.Top + 50
                .Show vbModeless
                Exit For
            End If
            .boolHiddenByUser = False
        Next pt
    End With
    End Sub

    In UPivotTable I added a QueryClose event to change the Close to a “hide” and note the form’s position:

    Public boolHiddenByUser As Boolean
    Public lngLeft As Long
    Public lngTop As Long
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        Cancel = True
        Me.Hide
        boolHiddenByUser = True
        lngLeft = Me.Left
        lngTop = Me.Top
    Else
        boolHiddenByUser = False
    End If
    End Sub

    I modified the ShowHide module. It doesn’t always re-create the form, but maybe that’s leaving it open to the problems you mentioned. It does keep the form position as set in the form’s QueryClose event:

    Private Sub ShowHideForm(Sh As Object, Target As Range)
    Dim pt As Excel.PivotTable

    With gufPivotTable
        .lngLeft = .Left
        .lngTop = .Top
        .Hide
        For Each pt In Sh.PivotTables
            If Not Intersect(Target, pt.TableRange2) Is Nothing Then
                If Not .boolHiddenByUser Then
                    .Show vbModeless
                    .Left = .lngLeft
                    .Top = .lngTop
                    Exit For
                End If
            End If
        Next pt
    End With
    End Sub

  2. How about a feature that lets you reorder each relevent pivot filter drop-down box so that currently selected items for each pivot field to the top of each relevent pivot filter drop-down box or to a (user-form masquerading as a pivot filter drop-down box)? It’s a real pain to have to scroll through say 1000 items just to see what you’ve got selected or uncheck some of them on the fly. Alternately it could list these in a userform that stays visible all the time.

    Or something that allows you to conditionally filter a pivot table… specifically when you’ve got a zillion things in a drop down filter, but only a handful in the pivot field…one of which you want to filter out. i.e. a custom filter using a listbox in a userform that is populated from the fields visible in the pivot table itself.

    Or a radio button that when checked forces pivottable filter drop down boxes open at the maximum size available so you can see as many options as possible, rather than having to resize them.

    Or a little button that makes a non-pivot chart out of the currently selected data range.

    I’ve been meaning to have a crack at doing this for ages, in order to have a real world problem to find out how to use userforms, but haven’t found the time…each time I sit down to do it the kids start fighting or an earthquake happens or I fall asleep!

  3. DK,

    Maybe, I missing something but…

    I’m not sure I understand your comment about global userforms. All userforms are global by definition. I almost always only hide a uf rather than unloading it. That way it retains all previous values.

    I have a few add-ins that show a uf modeless and the contents of the uf adjust to user actions. No hiding / unloading / recreating. The delay in doing so would not be acceptable to the customer.

    One thing that *might* be different from how I use ufs is that I *pretend* that all properties/methods of a uf are private and not available to me outside the uf code module. I add my own properties / methods and call them. They, in turn, deal with the specifics of the uf.

    That way, my event procedure calls the uf method and that does whatever it needs to do.

    The below doesn’t have any event procedures but it describes in more detail the above approach.

    Userform Interface Design
    http://www.tushar-mehta.com/publish_train/book_vba/09_userinterface.htm

Leave a Reply

Your email address will not be published. Required fields are marked *