WithEvents of Excel.Application Events

[DocAElstein]

Event coding in Excel spreadsheets(Application Events) Misc

Post 4: “Class events” Using the “Class way” to do it

There are a couple of pre subscribed and pre Instantiated event code added to a “Class module” after we insert a Class module.
They basically kick in when an object is built ( instantiated ) using the blueprint of the Class and when an object from the Class is Set to Nothing
A good way to understand their use which also helps understand the encapsulation, Encapsturbation , ideas, as well as the general use of Classes, particularly with even codes, is to consider re writing the event codes example of the last post in the way more preferred by computer professionals.
The main difference in the two ways we will see is that we use one of the two pre subscribed and pre Instantiated event, and must then also do some other things to cause it to kick off. ( We will see that we cannot easily set this code off directly )
Here is that code again, ( slightly modified to allow it to go in any object code module).
The code assumes you have a worksheet with tab name “Sht_1”. If you change the value in the first cell of that worksheet, “Sht_1” , then you will get a message.

Points to remember:…_
_.. You must run the routine to instantiate WsSht_1 ( Sub InstantiateWsSht_1() in last post ) once to instantiate the object WsSht_1
_.. There is no significance to the underscore, _ , in Sht_1 or WsSht_1 etc.. These are names we can choose. But as we have learnt, there is a significance to the _ and the SheetChange combination in _SheetChange , as this is a reserved events code character combination )
Events _SheetChange Worksheet change.jpg : http://i.imgur.com/6NY0WeC.jpg

Dim WithEvents  WsSht_1 As Excel.Application
Sub Instantiate____() ' Routine to Instantiate WsSht_1
 Set WsSht_1 = Excel.Application
End Sub
Sub WsSht_1_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 If Sh.Name = "Sht_1" Then
 If Target.Address = "$A$1" Then MsgBox prompt:="You just changed the value in the first cell in worksheet """ & ActiveSheet.Name & """ in the Workbook """ & ActiveSheet.Parent.Name & """"
 Else '
 End If
End Sub

Summary : This is generally what we want:
We want to make an object which we will name arbitrarily WsSht_1 . This is to be subscribed to the event codes of the current “open in front of us” Excel. In one of the event routines then available to this object, or rather already then there in this form:…._

Sub  WsSht_1_SheetChange(ByVal Sh As Object, ByVal Target As Range)

 End Sub

_... we will add coding so that it looks now like this:_......

Sub  WsSht_1_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   If Sh.Name = "Sht_1" Then
     If Target.Address = "$A$1" Then MsgBox prompt:="You just changed the value in the first cell in worksheet " & Me.Name & " in the Workbook " & Me.Parent.Name
   Else '
   End If
End Sub

Hence after running the instantiating code to “build “ the variable ( WsSht_1 ) , “into an object” , that object will “monitor” or “watch for” changes in a sheet, and take the action determine by our added coding.


_.___