Listing Experimenting with Workbook Object Events

Private Sub Workbook_Activate() If UseEvents Then

MsgBox "Welcome back!", vbOKOnly, "Activate Event" End If End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim lResponse As Long

If UseEvents Then lResponse = MsgBox("Thanks for visiting! " & _

"Are you sure you don't want to stick around?", _ vbYesNo, "See ya...") If lResponse = vbNo Then Cancel = True End If End Sub

Private Sub Workbook_Deactivate() If UseEvents Then

MsgBox "See you soon...", vbOKOnly, "Deactivate Event" End If End Sub

Private Sub Workbook_Open() Dim lResponse As Long lResponse = MsgBox("Welcome to the Chapter Six Example " & _ "Workbook! Would you like to use events?", vbYesNo, "Welcome")

If lResponse = vbYes Then TurnOnEvents True

Else

TurnOnEvents False End If

' save change to workbook ' so it doesn't bug us later ThisWorkbook.Save End Sub

Private Sub TurnOnEvents(bUseEvents As Boolean) On Error Resume Next If bUseEvents Then

ThisWorkbook.Worksheets(1).Range("TestEvents").Value = "YES"

Else

ThisWorkbook.Worksheets(1).Range("TestEvents").Value = "NO" End If End Sub

Private Function UseEvents() As Boolean On Error Resume Next

UseEvents = False

If UCase(ThisWorkbook.Worksheets(1).Range("TestEvents").Value) _ = "YES" Then UseEvents = True

End If End Function

Private Sub Workbook_SheetActivate(ByVal Sh As Object) If UseEvents Then

MsgBox "Activated " & Sh.Name, vbOKOnly, "SheetActivate Event" End If End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean)

If UseEvents Then

MsgBox "Ouch! Stop that.", vbOKOnly, "SheetBeforeDoubleClick Event" End If End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean)

If UseEvents Then

MsgBox "Right click.", vbOKOnly, "SheetBeforeRightClick Event" End If End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If UseEvents Then

MsgBox "You changed the range " & Target.Address & _ " on " & Sh.Name, vbOKOnly, "SheetChange Event"

End If End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) If UseEvents Then

MsgBox "Leaving " & Sh.Name, vbOKOnly, "SheetDeactivate Event" End If End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) If UseEvents Then

If Target.Row Mod 2 = 0 Then

MsgBox "I'm keeping my eyes on you! " & _

"You selected the range " & Target.Address & _ " on " & Sh.Name, vbOKOnly, _ "SheetSelectionChange Event"

Else

"You selected the range " & Target.Address & _ " on " & Sh.Name, vbOKOnly, _ "SheetSelectionChange Event"

End If End If

End Sub

Rather than type all of this in, I'd recommend copying it or downloading it from the website. I included a couple of procedures to enable the ability to turn the events on or off because after you spend a few minutes experimenting with them, they'll drive you nuts. In order for this functionality to work, you need a range named TestEvents on the first worksheet in the workbook. If this range is not present, the code associated with each event will always be executed.

If you are entering this code, be sure to enter it into the ThisWorkbook object. Also, don't change the name of the procedures. You can always spot an event procedure because it must begin with the name of the object that it is associated with, followed by an underscore, followed by the name of the event.

As you experiment with these events, notice that on some occasions, one action will generate more than one event. For example, switching to another worksheet in the workbook generates a sheet deactivate event for the sheet you're leaving followed by a sheet activate event for the sheet you activated.

This is what can make events so tricky to work with. Things really start getting complicated when you're using other objects that also have events. For example, the Worksheet object also has an Activate and Deactivate event. If you attached code to each worksheet object's events, the act of switching between worksheets could then cause four different event procedures to fire (execute). What tends to happen is that you add various event procedures that start to interact with each other in ways that you didn't anticipate. Once this happens to you, you'll appreciate (or learn to appreciate) the debugging skills I talked about in Chapter 4.

0 0

Post a comment