Event Sequence

Over on Chip Pearson’s download page, there’s a file called EventSeq.xls. It has a message box in every event so you can see what events get fired when and in what order. I use it at least a couple of time per year, sometimes to see the order events, but more often to see which event fires when I do something.

Someone sent me an email asking which event fires when you add a shape to a worksheet. EventSeq.xls to the rescue. The answer is: no event fires. As I was using that file, I got tired of clearing all those darn message boxes, so I tweaked things a little. Instead of message boxes, I log the events in a listbox. Since Chip did all the work, all I had to do was make a procedure to fill the listbox, then do a find and replace.

One problem I encountered using this method is logging the events that happen when you close the workbook. They all get logged, but the userform disappears so fast, you can’t see them. I ended up writing everything in the listbox to a CSV file when the file closes.

As long as I was in there, I added code for all the events through 2003.

Lastly, I added an Annotate button. This allowed me to stick a note in the listbox to deliniate when I did something. See the screen shots below.

Download EventSeq.zip and let me know what you think.

Here’s some code:

Public Sub LogEvent(sMsg As String)
    Dim vMsg As Variant
    Dim i As Long
    vMsg = Split(sMsg, vbTab)
    With ULog.lbxAppLog
        .AddItem vMsg(0)
        For i = 1 To UBound(vMsg)
            .List(.ListCount – 1, i) = vMsg(i)
        Next i
        .TopIndex = ULog.lbxAppLog.ListCount – 1
    End With
End Sub
Sub PrintLog()
    Dim i As Long, j As Long
    Dim sFname As String
    Dim sPath As String
    Dim lFnum As Long
    Dim sLine As String
    sPath = ThisWorkbook.Path
    sFname = “EventSeqLog.csv”
    lFnum = FreeFile
    Open sPath & “” & sFname For Output As lFnum
    With ULog.lbxAppLog
        For i = 0 To .ListCount – 1
            For j = 0 To .ColumnCount – 1
                sLine = sLine & .List(i, j) & “,”
            Next j
            Print #lFnum, sLine
            sLine = “”
        Next i
    End With
    Close lFnum
End Sub

Here’s Here are some screen shots:

input box for annotations
event log listbox

Posted in Uncategorized

6 thoughts on “Event Sequence

  1. Dick –

    Sounds like a nice enhancement. I used to use Chip’s workbook, but now I hack my own in whatever project I’m working on. I generally just create the event procedure outlines (or stubs, or whatever they’re called) and put a breakpoint on the End Sub. I generally don’t need a whole accounting of all of the events, I just need to know which one fires, or which order a selected few fire. Most of my projects have a log function that adds a line or two to a running log file, so I can call it from within the event procedures to get a record of the event sequences.

  2. Maybe adding one more button to the userform–cmdClear:

    Private Sub cmdClear_Click()

    Dim resp As Long

    resp = MsgBox(Prompt:=”You sure you want to clear the listbox?”, _

    If resp = vbYes Then
    End If
    End Sub

    To clear it when you want to.

  3. I enjoy every installment! I can’t resist though–your grammar is off. It should read “Here are some screen shots” not “Here is some screen shots”.

  4. Hi,
    I was looking for something like this, thanks for the nice write-up. It helped a lot! In addition to this, is it possible to log events like ‘user clicking on zoom-to-selection in view menu’, can such events be logged as well? It would be really helpful if you can provide some directions on this.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.