WithEvents of Excel.Application Events

[DocAElstein]

Another Short Example

Taken from this Blog post . https://www.exceltip.com/events-in-v...excel-vba.html
https://www.exceltip.com/events-in-v...#comment-50479
This example will bring up a message box telling you which worksheet you selected
05_ExcelTipExampleGig.jpg
http://i.imgur.com/rNaYcFr.gif

http://www.exceltip.com/wp-content/u...01/gif8-13.gif

I will do the example assuming we want the macro to be enabled by the opening of the workbook. I will do just one possible version of the Class way and the Non Class way

Class Way

In the ThisWorkbook object code module:
ClassWayExTipsThisWorkbookObjectCodeModule.JPG http://i.imgur.com/gxH1KEu.jpg

Private AppE As MyAppEvents
Private Sub Workbook_Open()
 Set AppE = New MyAppEvents
End Sub

In a Class Module, which is given the name MyAppEvents
ClassWayExTipsThisWorkbookClassCodeModule.JPG http://i.imgur.com/Iychi2a.jpg

Private WithEvents myApp As Excel.Application
Private Sub Class_Initialize()
 Set myApp = Excel.Application
End Sub
Private Sub myApp_SheetActivate(ByVal Sh As Object)
 MsgBox ActiveWorkbook.Name & "-" & Sh.Name
End Sub

ClassWayExcelTipExample.xls : https://app.box.com/s/rb7f51emtsamv18pklz358ux6cyozfkl


Non Class Way

In the ThisWorkbook object code module:
NonClasWayExTipsThisWorkbookObjectCodeModule.JPG http://i.imgur.com/l9SfDDV.jpg

Private WithEvents myApp As Excel.Application
Private Sub Class_Initialize()
 Set myApp = Excel.Application
End Sub
Private Sub myApp_SheetActivate(ByVal Sh As Object)
 MsgBox ActiveWorkbook.Name & "-" & Sh.Name
End Sub

NonClassWayExcelTipExample.xls : https://app.box.com/s/hllpmw3h9p277rgs6mi2321u47k2uhtb