I’ve been fooling around with workbook and application-level events in UserForms. I’ve put them to good use in one or two projects, so thought I’d post about it. While thinking about the best way to present it, I ended up with a form that allows you to track all application-level events in an Excel instance. EventTracker looks like this (click the pic to for bigger one):
Warning: meandering post ahead
Before we get to that though, here’s how you can create a simple form that responds to all SheetSelectionChange events. First add a userform to a workbook in the Visual Basic Editor, then add a textbox to it. Paste this code into the form’s code module:
Private Sub UserForm_Initialize()
Set app = Application
End Sub
Private Sub app_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Me.TextBox1 = Target.Address(external:=True)
End Sub
The code creates a WithEvents Application variable which is used to track application-level events. In this case, we’ll track the SheetSelectionChange event, but you can select other ones by selecting “app” in the dropdown at the top left of the VBA editor and the event in the dropdown at the top right.
Before you run the form you need to set its ShowModal property to False (or there will be no application-level events to track). Or you could just add this code to the code above:
Static Activating As Boolean
If Activating = False Then
Activating = True
Me.Hide
Me.Show vbModeless
End If
End Sub
I didn’t know the above would work until I tried it here. I don’t recommend it for any kind of serious coding, because of the static variable, but I like it. Basically the code only runs the first time UserForm_Activate runs. It hides the form and then shows it again modally, obviating the need to set the ShowModal property in design time or open the form from another module.
Now run the form and you’ll see that every SelectionChange event is reflected in Textbox1.
So, back to the EventTracker form. It has two listboxes, one that shows all the available events to track, and one that lists the events that have occurred. You can set the events to track to all or none, or just some. The listbox is set to MultiSelectExtended, which means you can use Shift and Control to select ranges. I also added code so that Ctrl-A selects the whole list. Plus there’s option buttons!
The listbox that tracks events gets resized as they are added. (The form is resizable as well.) It shows the event and its parameters, such as the name of the worksheet being activated. I changed the parameters slightly, separating out the “Target” argument for the SheetPivotTableUpdate event into a “Pivot” column. The code that adds a row to the recent events uses a paramarray, which is the first time I’ve ever used one.
Here are some of the things I learned while making, and running, the form:
1. Even when the “After pressing enter, move selection” option is off, the SheetSelectionChange event fires after you edit a cell and hit Enter. Who knew?
2. There are a bunch of new events in Excel 2010. I only included “WorkbookAfterSave,” “WorkbookNewChart” and “SheetPivotTableAfterValueChange” in this tool. The rest have to do with Pivot table OLAP cubes.
3. There’s still no BeforeReallyClosing event though.
4. The maximum number of items allowed in a listbox is subject to available resources. On my laptop that equated to 6,242,685, so I didn’t need code to handle overfilling the event tracking listbox.
5. I discovered Chip Pearson’s excellent form resizing API code and used it to set the form with a minimize button and the ability to be resized.
One bit of code that’s useful on its own resizes column widths in a listbox. It’s based on this Daily Dose of Excel post, which in turn was based on a very thorough post by Jan Karel Pieterse. I modified Dick’s code to include the headers in the resizing, and also to base the the resizing only on the listbox’s visible rows:
'Uses a hidden label in the form to hold
'text from headers and visible rows and
'resize to the widest one for each column
Dim ColWidths As String
Dim MaxWidth As Double
Dim i As Long
Dim j As Long
Dim VisibleRowsCount As Long
Dim HeaderLabels As Collection
Dim HeaderLabelWidths As Double
'create the HeaderLabels collection
Set HeaderLabels = GetHeaderLabels
With Me.lstRecentEvents
'skip the code if no rows yet
If .TopIndex > -1 Then
VisibleRowsCount = Application.WorksheetFunction.Min((.ListCount - .TopIndex) - 1, .Height / lblHidden.Height)
For i = 0 To .ColumnCount - 1
'first get the header label width
Me.lblHidden.Caption = HeaderLabels(i + 1) & "MM"
MaxWidth = Application.WorksheetFunction.Max(Me.lblHidden.Width, MaxWidth)
'only want to resize for the visible rows
For j = .TopIndex To .TopIndex + VisibleRowsCount
Me.lblHidden.Caption = .Column(i, j) & "MM"
MaxWidth = Application.WorksheetFunction.Max(Me.lblHidden.Width, MaxWidth)
Next j
ColWidths = ColWidths & CLng(MaxWidth + 1) & ";"
HeaderLabels(i + 1).Left = HeaderLabels(1).Left + HeaderLabelWidths
HeaderLabels(i + 1).Width = MaxWidth
HeaderLabelWidths = HeaderLabelWidths + CLng(MaxWidth + 1)
MaxWidth = 0
Next i
.ColumnWidths = ColWidths
End If
End With
End Sub
Back to the EventTracker form again. As my father might say “The road is the destination.” Or is it “kindling is fire…?”
The downloadable zip file has a grid showing the events handled and a handy button to start Event Tracker. The workbook is .xlsm format, because if it was saved in .xls then the more recent events wouldn’t be available. You can still run it in XL 2003 as long as you’ve installed the compatibility pack. When running in an earlier version than 2010, events not supported in that version show up in the form module under “General,” rather than under “app.”
Nice stuff. One event handler that I wished existed was a pivotfield dropdown event…while this triggers a pivot refresh, there’s no way of telling what pivotfield dropdown was selected.
IMHO, MS should add core functionality like this, rather than some of the bling they stuff into new versions.