Understanding events. III: Some useful stuff

The whole FAQ consists of four parts
the basics
automation events (this one)
some useful stuff
a substitute for control arrays in VBA

As it was told, events can be handled only in class modules, for instantiated objects. But no one told that we canÆt use existing object modules, no one told that we have to use default object module event handlers.
Some examples:
1.
To trap application events (still in excel) just enough in ThisWorkbook module:

[tt][color blue]Private WithEvents App As Application
æ now App available in the left combo

Private Sub Workbook_Open()
Set Me.App = Application
End Sub

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
MsgBox "application event: " & Wb.Name
End Sub[/color][/tt]
simpler?

2.
Selective switching of events instead of conditional handling. In some cases it is a good replacement for rather bold Application.EnableEvents, touching all excel events. In ThisWorkbook module (can also be in Sheet1 module):

[tt][color blue]Public WithEvents Wks1 As Worksheet
Public WithEvents Wks2 As Worksheet

Private Sub Wks1_Activate()
æ event code
End Sub

Private Sub Wks2_Calculate()
æ event code
End Sub[/color][/tt]
and somewhere else in the code:
Set Wks1=Sheet1 æ switch Activate event for Sheet1
. . . .
Set Wks2=Sheet1 æ switch Calculate event for Sheet1
Set Wks1=Nothing æ switch off Activate event for Sheet1
. . . .
Set Wks2=Nothing æ switch off Calculate event for Sheet1[/color][/tt]

3.
Excel as a painting tool (?):
A small userform with:

[tt][color blue]Private WithEvents wks As Worksheet

Private Sub UserForm_Initialize()
Set wks = ActiveSheet
End Sub

Private Sub UserForm_Terminate()
Set wks = Nothing
End Sub

Private Sub wks_SelectionChange(ByVal Target As Range)
Target.Interior.ColorIndex = Rnd * 56
End Sub
and a standard module with:
[tt][color blue]Sub ShowModelessForm()
UserForm1.Show vbModeless
End Sub[/color][/tt]

Now make active sheetÆs cells square, set zoom to 50% or 25% and run ShowModelessForm. Interesting?

4.
You can use full power of automation. Have you considered referencing PowerPoint library in excel and running it together with workbook this way? ThisWorkbook module:

[tt][color blue]Private WithEvents ppApp As PowerPoint.Application

Private Sub ppApp_PresentationNewSlide(ByVal Sld As PowerPoint.Slide)

End Sub

Private Sub Workbook_Open()
Set ppApp = New PowerPoint.Application
ppApp.Visible = msoTrue
End Sub[/color][/tt]