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:
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:
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.
Maybe adding one more button to the userformcmdClear:
Private Sub cmdClear_Click()
Dim resp As Long
resp = MsgBox(Prompt:=”You sure you want to clear the listbox?”, _
Buttons:=vbYesNo)
If resp = vbYes Then
Me.lbxAppLog.Clear
End If
End Sub
To clear it when you want to.
Useful stuff Dick, thanks.
I enjoy every installment! I can’t resist thoughyour grammar is off. It should read “Here are some screen shots” not “Here is some screen shots”.
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.
Thanks.
An all around incredibly written blog post!!