Userform Application-Level Events

Userform Application-Level Events

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):

EventTracker in Action

Warning: meandering post ahead

curves 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 WithEvents app As Excel.Application

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:

Private Sub UserForm_Activate()
Static Activating As Boolean
If Activating = False Then
    Activating = True
    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.

Simple event tracker form

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:

Private Sub SetLstRecentEventsColumnWidths()

'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

everywhere you go ...
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.”

Event tracker workbook

One thought on “Userform Application-Level Events

  1. 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.

Speak Your Mind

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

To post code, do this: <code> your vba here </code>