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:
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 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?
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:
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 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:
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
Can you please fix my last code section?
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!
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
UserForms are ancient….you should try and create a Custom Taskpane
sam: no keyboard support that I’m aware of.