WithEvents of Excel.Application Events

[DocAElstein]

(This is post 2294)
There is a summary / revision in post #6 https://excelfox.com/forum/showthread.php/2294-WithEvents-of-Excel-Application-Events?p=15200&viewfull=1#post15200 This will most likely only be useful as a revision later, but might also help some who have perhaps already tackled this subject and need quickly to revise the Format of a typical solution



WithEvents of Excel.Application
Post #1 Introduction and Revision: How we get macros to run automatically…
In Excel things get done. We tend to use the jargon: " "Events" occur ". Usually to do this some internal and/or high level coding is in action to do this. In VBA coding we have the option to "hang on" procedures to this coding so that we can arrange that other things are done with or at some point within these "Events" . The end effect is that we can "automate" things, that is to say some of our coding / procedures can be started automatically when something else happens/ when an event occurs.

Post #1: Review of some relevant basic programming concepts
It is difficult to explain this subject without reviewing some other somewhat imprecisely defined concepts…
This Tutorial Thread, WithEvents of Excel.Application , assumes you have some initial experience and knowledge of event type code things. In other words you are familiar with things like coding procedures which start automatically when you change things in a worksheet. So you may have done some simple code like this - ...._
Private Sub Worksheet_Change(ByVal Target As Range)
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
End Sub_....

[DocAElstein]

WithEvents of Excel.Application. Encapsturbation

Post #2: Excel Events. Hanging codes on to them
As discussed in the referenced summary, http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page10#post10838 , the vaguearities of the Objects concept in Object Orientated Programming , OOP , leads to the idea of certain code modules being referred to simultaneously as both belonging to an object , and being the object itself…
This has and continues to cause computer engineers to confuse themselves, and in an attempt to make sense of order in a messed up system, they often organise their use of code modules in some way that gives at the appearance of some sense and order or theory..
Generally it is considered that things happen in Excel, that is to say Excel “works”, as a result of Events occurring to objects. Different objects will have different events associated with them
Those points are important to accept and bear in mind to avoid confusion in the following notes.

Organising and finding the Event routines, Encapsturbation
If we were very clever and had access to all possible Microsoft software then we could probably use some VBA or similar OOP coding to make ourselves the “Excel in front of us”. Some of that has been done for us to get us started.
As a side note, based on the introduction above, we note that Event routines will generally either be in, or chosen to be in, or only allowed to be put in “object modules”. Programmers will often talk about “Encapsulation” or keeping things “where they belong” without being specific but attempting to sound specific and organised in a disorganised situation. It can be considered as a form of Encapsturbation

Worksheet Events. The Workbook Events
These are the events codes most often encountered and often they are the only ones explained under the Theme of events codes. But in fact they are just a small selection of event codes whereby they have been partially already put in place in a way that only loosely ties up with some general order.
A example, consider a Worksheet object. This thing is simultaneously regarded as the spreadsheet of that worksheet or the worksheet code module. We do not have to make this: This has been pseudo done for us, and is somehow part of the Excel software which we purchased. When we run excel something pseudo of this form has been done , or is done, as the software starts up
_ A class object of a Worksheet has been made. Possibly one is made for each of the default worksheets that appear. (The number may vary depending on your version of Excel ) So there exist somewhere a very complicated class object and/or class object module , or modules, which we can’t see with the name Worksheet.
_ A few worksheet objects are made from that class, and for the case of a worksheet, they are “subscribed” to the Events associated with a worksheet. Internally some variables, say for the case of the first worksheet, Sheet1 , will be used pseudo like
Dim WithEvents Sheet1 As Worksheet
_ Set Sheet1= New Worksheet
Briefly, those two lines do the following:
the Dim gives some initial indication of the type of thing that Sheet1 refers to. This might mean referring to the text/ Blueprint info of the variable type of setting aside a copy of such. In any case it does not apparently require too much computer memory. The variable state, for the case of an object, is typically regarded as Nothing , at this point.
It is usually considered that a more substantial computer memory is used as a result of Set and New pair. This goes by the name of “instantiation” or similar and is regarded as “making” or “building” the object.
The exact coding and resulting structure of objects is not clear, that is to say not given openly, and it is not permitted to do those steps in coding for the case of a Worksheet!!!. We are not allowed to instantiate a Worksheet. It is possible that the coding results in some pseudo structure of like
_________________Workbook.xl__
____________ Worksheets Collection Worksheet Class
____ Class Sheet1 ___ Class Sheet2 _____ Class Sheet3
_________ Sheet1 ___ _ _____Sheet2______ _____ Sheet3

It is not clear or disclosed as which of the lower two levels we actually “see” in the code window or the spreadsheet. We see something of, and have some access to , the Sheet1, Sheet2, Sheet3 type “things”. Sometimes the lower two levels might be considered as the same. Sometimes the code module might be considered as the Class Sheet1 and the spreadsheet as Sheet1.
So it is all a bit Unclear and open to interpretations.
Consider some points:_
_ General coding is permitted in the worksheet code modules in addition to the Event codes. General coding is not permitted in the Class module object which we can create.
_ We cannot subscribe a variable to the events of a Worksheet, or rather we are permitted to do it from the syntax, but the resulting codes simply do not work!!!…. ( see macro below )
_ We do not need to do the following to step through a code placed in such a module via F8:
Tools Options.JPG : http://i.imgur.com/KZUyPnB.jpg
Break in class modules.JPG : http://i.imgur.com/75Eg6UE.jpg
So those three facts might suggest that we are at the lower level … better described as an object than a class module

The story is similar with the object for a workbook. We have a single code module which probably fits into a structure of
______________________Excel Application
______________________Workbook Class
_______________________Workbook.xl__
Once again we cannot instantiate a Workbook. We can write normal codes in the code module that we “see” , etc.. etc… So similar arguments to the worksheet case suggest what we see is a ( single in this case ) object module

Encapsturbation Attributing Encapsulation
Encapsturbation is a combination of general and self inflicted actions attempting to give a seeming order to the situation. Attributing factors , rules and general practice are as follows.
Firstly and not always immediately for any obvious reasoning, ( other than Encapsturbation ) , a couple of things related to where things go and how to get at the codes
_ Where to put WithEvents
.(i) WithEvents can only go as procedure level variable. ( So outside of a routine, and placed towards the top of a code module). This will make some sense after writing practical routines: The object must “exist” in order to “catch” events, so a variable inside a routine will be of limited use as the variable will “go out of scope”, that is to say no longer “exist” after the routine ends.
.(ii) WithEvents can only go in an object or class object code module.
_( Drop down list, help , pseudo intellisense
For no obvious particular logical or scientific reason there are a couple of drop down list in code modules. (We mentioned in the previous post that for our common case of the worksheet, it seemed to somehow conveniently fit in ). For object modules you typically can select in the left list between two main heading types.
One type is meaning general routines and there is just one of those.
The other type refers to the routines for all the WithEvents objects variables .
Selecting the general will result in a list in the right drop down of all normal procedures in the code module.
Selecting the object listed which is subscribed to events will result in a long list in the right drop down of all available routines. This probably does make some sense: Once subscribed the routines are “hooked in” and there. We can choose to add code lines in if we wish. One does come in automatically when you select the object: You need to have the cursor within a code in order to get the relevant list available in the right drop down list: One comes in when you click on the object in the left drop down box. The others come in when you click on them. I would personally have put them all in to avoid confusion, but maybe someone thought it would just look tidier to leave them out until they were actually added to …
In the following code and screenshots I am in a worksheets object code model and have subscribed an object variable, WsTyp2 , to the event codes of worksheet. This appears to be accepted syntaxly and a second duplicated set of codes are included in the right drop down list. ( The Event routine , Private Sub WsTyp2_SelectionChange(ByVal Target As Range) , using WsTyp2 appears however to be complete without function. This is probably as we are not permitted to Set it to a New instance ). Even when assigning to the existing instance, with , Set WsTyp2 = Me , we still find that the Events code using the subscribed object, WsTyp2 , does not respond to any event . Furthermore neither event code works in a module other than a worksheets code module, which suggests that some form of class object is present which we have no access to determines how the existing events cods work )
Worksheet Events.JPG: http://i.imgur.com/vq9gQ9u.jpg , WsTyp2 Events.JPG : http://i.imgur.com/GWSNDS9.jpg



Dim WithEvents WsTyp2 As Worksheet
Sub MakeAWorksheetNot()
On Error GoTo Bed:
Set WsTyp2 = New Worksheet ' Errors
Set WsTyp2 = Me
Exit Sub: ' Code end without error
Bed:
MsgBox prompt:=Err.Number & vbCrLf & Err.Description: Debug.Print Err.Number & vbCrLf & Err.Description ' Ctrl+g from the VB Editor to get copyable message in the Immediate window
End Sub
Private Sub WsTyp2_SelectionChange(ByVal Target As Range)
MsgBox prompt:="You will never see this"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox prompt:="You will see this if you select a cell in worksheet with name" & vbCrLf & Me.Name
Resume Next
End Sub


_.____

Application Events
This is probably the better start point as these codings have not been tampered , manipulated , prepared in the somewhat hap hazard way that the those already discussed have. As such it is a bit clearer how to go about using them. They can be thought more generally, or more fundamentally, as Event coding and Excel Workbooks

[DocAElstein]

Post 3: Event coding in Excel spreadsheets. Application Events
The simple ( non class ) way to do it

Coding like that discussed in this post are often referred to loosely as “Application Events”. In this thread I have been trying to show that they are a more general and fundamental usage of the event coding to which the more typically known worksheet event type codes belong. The more typically known event codes may sometimes be referred to loosely as “normal event codes” or “standard available event codes”

The general rules and Encapsturbation ideas etc. as discussed so far apply to “Application Events”.
_(i) WithEvents can only go as procedure level variable. ( So outside of a routine, and placed towards the top of a code module).
_(ii) WithEvents can only go in an object or class object code module.
_(i) and _(ii) must be adhered to or a syntax error will arise. (i) makes some sense: In a routine it would not be much use as a variable for an object subscribing to, and therefore “watching for” , events as it would cease to exist at the end of a the routine.
_(iii) In addition, and further in line with Encapsturbation , it is general practice to insert and use a class module for all or most of the coding relating to a specific use of Application Events. This is a general practice, but is not required.

As a simple example, consider a simple equivalent to the very first example shown at the start of this thread. We had the following routine in a worksheets code module. ( We were using one of the already available “standard” worksheet event codes). Once again, just for convenience, we make use of Me , so the coding is intended to be used in a code module of a worksheet: Changes in that worksheet are effectively monitored with the event code.
Here again is that “standard event” routine:
Right mouse click Or double click in VBA explorer Project window to get code module.JPG : http://i.imgur.com/gsz6s2N.jpg

Private Sub Worksheet_Change(ByVal Target As Range)
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
End Sub

Here is some equivalent coding using “Application Events”
The first routine , Sub InstantiateWsMe() , needs to be run once to “make” the object, WsMe.
The second routine , Sub WsMe_SheetChange(ByVal Sh As Object, ByVal Target As Range) , will then run automatically when a cell in the worksheet with name given by Me.Name has its value changed.
Custom Listener_Sheet Change.JPG : http://i.imgur.com/kyE0qKh.jpg


Dim WithEvents WsMe As Excel.Application ' This must go outside any routines, towards the top of a code module. The code module must be an object module or a class object module. These are rules ( for no particular reason that I can think of). If you do mot adhere to them you will get a syntax error. The first is possibly reasonable: In a routine it would not be much use as a variable for an object subscribing to events as it would cease to exist at the end of a the routine.
Sub InstantiateWsMe() ' You must run this once to get the following code to work. If you add or delete any codes in this code module, then you must re run this routine
Set WsMe = Excel.Application ' Important not to use New here, as we want to subscribe to the current instance of Excel in front of us.
End Sub
Sub WsMe_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = Me.Name Then ' Then we are in this worksheet. Chenge Me.Name with the worksheet tab name like "Sheet1" or "MySheet" etc. for code to monitor a a particular worksheet
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

Sub TestieCalls() ' This can be used to test the event code
Call WsMe_SheetChange(Me, Me.Range("A1"))
End Sub

Coding of the above form is often referred to loosely as a “listener” or more often “custom listener” when using “Application Events”
( The above coding needs to go in a Worksheets code module, but only because I am using the Me for convenience to get at a worksheet. Generally such codes can go in any object code module, ( or Class object code modules ) )
_.____

Some, not all, event codes have things passed to them in the brackets (___), ( using coding we can’t see ) when they are started. The one used here is passed the Sheet object in which the cell or cells are changed, as well as the Range object of the cells or cells which are changed.

( The last code , Sub TestieCalls() , allows us to test the event code )

_.___

For the case of the original code from this Thread, effectively a code line like Dim WithEvents WsMe As Excel.Application and a routine like Sub InstantiateWsMe() were effectively done for us, and we had part of the main code like this in each worksheet code object module:

Sub WsMe_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = Me.Name Then

Else '
End If
End SubThe above could be considered as what is behind the following in a worksheet code module, and so is a direct equivalent to it:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
It is not clear if any coding is shared in the application events and the normal events: Although a direct comparison was demonstrated in this thread with the routines, Sub WsMe_SheetChange(ByVal Sh As Object, ByVal Target As Range) and Private Sub Worksheet_SelectionChange(ByVal Target As Range) , I don’t think you can do everything with the application events that you can with the normal events. I expect some coding is shared, and , as shown, in some cases we can come close to duplicating normal event codes with application event codes.


_.________________-

Just to summarise: This “standard” events routine , which one may become familiar with at an early stage in learning VBA …._

Private Sub Worksheet_Change(ByVal Target As Range)

‘ We add codeiX here

End Sub_.... can be considered as equivalent to Excel having already “done and run**” something like this “application event” coding: ( ** Sub InstantiateWsMe() mist have been run at least once )


Dim WithEvents WsMe As Excel.Application
Sub InstantiateWsMe()
Set WsMe = Excel.Application
End Sub
Sub WsMe_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = Me.Name Then

‘ We add codeiX here

Else '
End If
End Sub


That concludes the main part of the Thread on Application Events

Summary : This is what is happening/ This is how it works:
We made an object which we named arbitrarily WsSht_1 . This is subscribed to the event codes of the current open in front of us Excel. In one of the event routines now available to this object, or rather already there in this form:…._

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

End Sub _... we added 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 we were using , ( 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.


The next posts have a few relevant further things which I may add to from time to time. In particular, we will do the same in a slightly different way, the “Class” way

[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.


_.___

[DocAElstein]

(Post 5 Part 4b)
Using the “Class way” to do it”

The “Professional Obfuscation“ Class way to do it
From the last Post:
Summary ( again ) : This is generally what we want:
We want an object which we 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 be adding coding so that it looks then 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.



So now we will do the equivalent coding which is more typically done by professionals to do the same as that coding.
First a Class module is added and its name changed to , say, Custom_WsChange. This name, including the underscore, _ , is of arbitrary choice. Right mouse click in VB Explorer Insert Class Module.JPG : http://imgur.com/1X1APHk.jpg

The coding we put in this new Class module almost identical to that from the previous post.
Class Custom_WsChange.JPG : http://i.imgur.com/GemN8yw.jpg
Class : _ Custom_WsChange http://i.imgur.com/1ldUusp.jpg

Dim WithEvents WsSht_1 As Excel.Application
Private Sub Class_Initialize() ' 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
The only difference is that the instantiate routine, ( Sub InstantiateWsSht_1() from the last post ) , is now in one of the two available pre subscribed and pre instantiated event code of Class Class Initialize.JPG : http://i.imgur.com/v5mKWhE.jpg

Private Sub Class_Initialize()

End Sub

At this stage we have the main equivalent coding to previously.
In the previous coding we needed to run Sub InstantiateWsSht_1().
The immediately obvious requirement now would be to run the equivalent Sub Class_Initialize()
But now we cannot run ourselves Private Sub Class_Initialize() : We cannot run directly any coding in the Class : The coding in the Class module that we can see, is effectively part the internally held “blue print” instruction information used to build objects from / of that Class . The information defines what is in an object made from that Class.
The key to getting this working is in building an object from this Class, Custom_WsChange. That may not be immediate obvious, but it will become apparent why later.

Build the “watcher “ object, WsSht_1 in the “Class way”
In any normal code module or object code module, we need to write the code lines to build an object of class Custom_WsChange
The first part of this will be to declare ( Dim ) a variable to the type of Class Custom_WsChange.
( One notes that intellisense will now offer us our newly made Class PrivateWatcher.JPG : http://i.imgur.com/5CTnREW.jpg )
Syntaxly, the Dim can go inside a routine , but it would not be much use as a variable for an object subscribing to events, and therefore “watching for”, as it would cease to exist at the end of a the routine. To go with this Dim , we need the some way to instantiate the object, ( “build it”).
This coding will do on any normal or object module
Watcher.JPG : http://i.imgur.com/fBFc5LE.jpg

Private Watcher As Custom_WsChange
Sub InstantiateWsSht_1()' Routine to Instantiate Watcher and thereby indirectly Instantiate WsSht_1
Set Watcher = New Custom_WsChange
End Sub
So that routine will need to be run once. I give it , ( by my choice ) , the same name as the (only) instantiating routine from the previous “non class way” , Sub InstantiateWsSht_1(). I do this because that is what it in effect finally does: This routine actually instantiates the object Watcher . That causes the routine in the Class module Private Sub Class_Initialize() to kick in. This is the action we can no longer do ourselves. This action is the main action that the “Class” is doing in the case of the this “Class way” of doing it”
Once this has taken place, we are where we were with the “not using Class way” :
Just to explain again: Here we build an object ( Watcher ) of Class Custom_WsChange . As this “comes into life” , its Sub Class_Initialize() event code builds the object WsSht_1.
( That code , Sub WsSht_1_SheetChange(ByVal Sh As Object, ByVal Target As Range) , must be in the Class module so that it becomes part of the object made from this Class : Generally codes in here become properties/ methods of the object built from the class. As such the functionality or actual coding is in a lower object comparable to our available worksheets code modules, but we cannot see it. Anyone that knows that in more concise detail is not telling, so that’s as good an explanation as you will get.


_.________________________

It is worth reminding ourselves / reviewing what we have done, ( only slightly differently this time), in the “Class way”
We are using both
pre subscribed and pre Instantiated event code
and
are own event coding using Application Event coding.
We have chosen to use a Class module for some of the coding.
This is what is happening/ This is how it works:
We make an object which we named arbitrarily Watcher . As this object “comes into existence” it in turn builds our object WsSht_1. This is subscribed to the event codes of the current open in front of us Excel. In one of the event routines now available to this object, or rather already there in this form:…._

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

End Sub _... we added 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 the , ( automatic this time ) running of the instantiating code to “build “ the variable we were using , ( 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.
So the “Watcher” code is “there” ( “somewhere” ) as before. The slight difference to the case of previously having that code in a “see able” object code module, is that we cannot see the actual coding in the object WsSht_1 this time: What we see as the Class module which we added, is “one level back up” the OOP chain. It is the Class Custom_WsChange which determines how the object looks. But Excel just doesn’t show us the code module as it does for the objects made from Class Worksheet and ThisWorkbook . That is just the way someone at Microsoft decided to organise it.
_......
We are very close now to the final solution often given for such a requirement. We need to take it just one small step further now in order to reach the coding typically presented in such solutions.
_......
###If we take this a small step further we finally come to the point at which we often get plunged into the deep end when using application events:
In a typical use, the code to instantiate would be put in one of the pre subscribed and pre Instantiated event code in the ThisWorkbook code nodule.

With so many different event codes flying around and a Class module for good measure, its not surprising that a given solution or Tutorial on this subject often confuses.

Purely for convenience, we would typically choose the pre subscribed and pre Instantiated event code which “monitors” or “watches for” the opening of the workbook. In this particular case, that description does not suit too well. A better description would be that the following routine is more likely hard written in the opening software permanently, so that this will always be done, regardless of whether we add any coding into it or not. This latter description is also as valid as any for all event coding.
( To revel this code module, double click on the ThisWorkbook ( German Excel DiesesArbeitsmappe ) shown in the VBA Project Window )
ThisWorkbook Workbook_Open.JPG : http://i.imgur.com/kWtgiu9.jpg

Private Sub Workbook_Open()

End Sub

So we would simply do this,


Private Watcher As Custom_WsChange
Private Sub Workbook_Open()
Set Watcher = New Custom_WsChange
End Sub

We do not have to put the variable , Watcher , in this code module: it could have remained in any normal code module as a normal global variable to be accessed from anywhere. But for general tidiness, Encapsturbation , and probably sometimes for other indirect good programming reasons, it would be put near the coding which instantiates the object, Set Watcher = New Custom_WsChange

_._______________

A Final shortened, simplified, summary of the typical complete Class way

The workbook is opened.
This causes via a ThisWorkbook event code ( __Workbook_Open() ) , an object to be made from a type / Class which we have pre determined by adding a Class module and putting code in it
That object being made from that class in turn results in another object being made which is subscribed to events of the Excel application, ( the “Excel we see in front of us” ).
Within one of the available event codes we have added coding such that changes in a worksheet are “monitored”

[DocAElstein]

Walkthrough revision. No explanations
I tried hard in the last post to start at the beginning and give clear concise explanations. ( I started doing things in the simpler way, which is rarely explained anywhere***. I finally did the more advanced “class way”).
Inevitably it turned into a long Blog, and it is easy to get confused, especially as my approach does not tie up with most other Blogs on the subject.
In this post I will try to avoid as much explanation as possible, and concentrate on walking through the same example again that I have used throughout. I will do it in the 2(4) ways, giving finally a workbook for each way. One example will be of the Class Way, and three will be of the Non Class Way.
I will start with the more advanced way, the class way. ( In most Blogs that is the only way shown*** ).
I will then do 3 examples of the simple ( non class ) way
I will assume I want the thing to be up and running automatically as soon as I open the workbook. ( So this means that in all example I will have to make some use of the already as standard available Private Sub Workbook_Open() which can be got at via the drop down lists in the ThisWorkbook object code module
ThisWorkbook Workbook_Open.jpg : http://i.imgur.com/10Bp8Wp.jpg , http://i.imgur.com/P6TE7uf.jpg

What do we want.. / the previous example
The example previously considered want to do something similar to this standard already available event macro.

Private Sub Worksheet_Change(ByVal Target As Range)
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
End Sub

I want to do this , purely as an example, using the WithEvents of Excel.Application ideas of this Thread


The Class way
In the Class way we make a blue print of the coding type we need. This needs to be in a class module. ( We wont actually get to see the coding copy finally used)
Insert a Class module , and give it some arbitrary name
InsertAClassModule.JPG , NameAClassModule.JPG
http://i.imgur.com/Op5rhAV.jpg , http://i.imgur.com/Cxx7nxZ.jpg
We need the three main code parts in this class module:
_ the dimming with events of an arbitrary named variable;
_ the instantiating of that variable;
_ the event coding.
Class Custom_WsChange.JPG : http://i.imgur.com/GemN8yw.jpg
Class : _ Custom_WsChange http://i.imgur.com/dZgJ5M7.jpg http://i.imgur.com/1ldUusp.jpg

Dim WithEvents WsSht_1 As Excel.Application
Private Sub Class_Initialize() ' 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 = "Sheet1" 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
We cant use / run any of those macros, because they are just a Blue Print of coding to be made. The way that class things work is that when we instantiate a variable , that is to say Set it to being of the class, then effectively we get a “real” set of the macros, ( although we can’t see them )
Either
_we can do that all in the ThisWorkbook like this
VariableAndSetClassInThisWorksheetObjectCodeModule .JPG http://i.imgur.com/GwvKUI7.jpg

Private Watcher As Custom_WsChange
Private Sub Workbook_Open()
Set Watcher = New Custom_WsChange
End Sub
Or
_ alternatively we can put the Private Watcher As Custom_WsChange in a normal code module
VariableInNormalCodeModue SetClassInThisWorksheetObjectCodeModule.JPG : http://i.imgur.com/nSHMDe7.jpg

By Setting the variable , Watcher , we effectively create an “invisible” copy of those macros, and at the same time the Instantiating Initialise macro is done

ClassWay.xls https://app.box.com/s/5hkwnz9c5lvudne5b1418qukt830nd0w

Simple ( non Class ) ways
The instantiating done in the Class way can be thought of as having the effect of making an object and object code window ( which we cant see) containing the main macros and at the same time causing the Private Sub Class_Initialize() ' Routine to Instantiate WsSht _1 to run
The simpler ways are possible because
_we already have some object code windows which we do see, and which we can add the actual coding to.
and
_ either by design or by accident, we find that the various with event things are available and seem to “work” as if we were in the “invisible” object code module produced by the instantiating in the Class way

We can probably come up with about half a dozen simple ways to do this, based on where various codings are put. We will consider 3 examples


Simple ( non Class ) way. (Example 1)
We can put the three main code parts in an existing object code module, for example in any worksheet object code module. ( It does not have to be that of the worksheet to which the coding is applied to). For example, we put the main macros in the second worksheet object cod module
SimpleWay1MainMacros.JPG : http://i.imgur.com/REXWgek.jpg
We see that due to the with events declaration, we have the required event macro available in the drop down lists
SimpleWay1AvailabeEventMacrosAfterWithEventsDeclar ation.JPG : http://i.imgur.com/dOompwu.jpg
The only minor difference is that the macro set off previously to instantiate the variable that is subscribed to the Excel application events will be a normal routine. (We will have to arrange that this macro is done).

Dim WithEvents WsSht_1 As Excel.Application
Public Sub WsSht_1_InitializeIt() ' 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 = "Sheet1" 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
We no longer need any variable to be set to that of any class. Instead we effectively short circuit that step, and the Workbook_Open macro inside the ThisWorkbook object code module , Calls directly the actual macro Sub WsSht_1_InitializeIt().
Note: we need to add the object having the coding to the Call:
CallInstanciateSimpleWay1.JPG http://i.imgur.com/EDWm5rS.jpg

Private Sub Workbook_Open()
Call Worksheets("Sheet2").WsSht_1_InitializeIt
End Sub
SimpleNonClassWay1 : https://app.box.com/s/naxfwg3ms8omvpcybrjpzjdw3o7i4w36


Simple ( non Class ) way. (Example 2)
This is a minor variation on the last way.
We can put the Sub WsSht_1_InitializeIt() instantiating macro in a normal code module.
The Workbook_Open macro inside the ThisWorkbook object code module remains similar , ( assuming we still use the same procedure name, WsSht_1_InitializeIt() )
We can either omit the object before the macro name, or replace it with the name of the normal code module.
SimpleWay2ThisWorkbookObjectCodeModule.JPG http://i.imgur.com/wyLsCjq.jpg

Private Sub Workbook_Open()
'Call WsSht_1_InitializeIt
' or
Call Modul1.WsSht_1_InitializeIt
End Sub

We will need to make two other small but important changes:
_ in the object code module containing the with events declaration: We must change Dim WithEvents WsSht_1 As Excel.Application to Public WithEvents WsSht_1 As Excel.Application
SimpleWay2ObjectCodeModule.JPG http://i.imgur.com/gq4OJuE.jpg

Public WithEvents WsSht_1 As Excel.Application
Sub WsSht_1_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Sheet1" 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
_ That last change above will let the variable be referenced outside of the object code module in which it is. We then need the minor modification to the instantiating macro in the normal code module so that it refers to the object:
SimpleWay2NormalCodeModule.jpg : http://i.imgur.com/rxpZXDk.jpg

Sub WsSht_1_InitializeIt() ' Routine to Instantiate WsSht_1
' Set Worksheets("Sheet2").WsSht_1 = Excel.Application
' or
Set Sheet2.WsSht_1 = Excel.Application
End Sub


SimpleNonClassWay2 : https://app.box.com/s/l1dtodagtappmdffagm4ktedo1qzgh2c


Simple ( non Class ) way. ( Example 3 )
( Everything in the ThisWorkbook object code module )
This is probably the simplest way.
We use the ThisWorkbook object code module for all the main macros. The Instantiate macro is the Workbook_Open macro
AllMacrosInThisWorkbookObjectCodeModule.JPG http://i.imgur.com/2fzKaAC.jpg

Public WithEvents WsSht_1 As Excel.Application
Private Sub Workbook_Open() ' 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 = "Sheet1" 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
We need no other macros anywhere!
SimpleNonClassWay3(AllCodingInThisWorkbookObjectCo deModule) : https://app.box.com/s/rya16vhhyigg7466l6z1v2iudvbpcliz

[DocAElstein]

Another Short Example
Taken from this Blog post . https://www.exceltip.com/events-in-vba/how-to-create-application-level-events-in-excel-vba.html
https://www.exceltip.com/events-in-vba/how-to-create-application-level-events-in-excel-vba.html?unapproved=50479&moderation-hash=f45c6d6c5af8658aba7b04a627809511#comment-50479
This example will bring up a message box telling you which worksheet you selected
3469
http://i.imgur.com/rNaYcFr.gif
https://i.imgur.com/rNaYcFr.gif
http://www.exceltip.com/wp-content/uploads/2020/01/gif8-13.gif
https://www.exceltip.com/wp-content/uploads/2020/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
https://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
https://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
https://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

[DocAElstein]

Further working examples
Disable Excel Close Button/ Control closing
( Post 8 )
Part #5 Disable Excel Close Button/ Control closing Cancel option Event code example
We can do another example as a solution to a question to “Disable Excel Close Button”. This is a good example of using an Event routine which has the Cancel option available

Part #5A Using “normal event routines” or “standard available event routines”
Event routines of the kind required for controlling Workbook events can typically be “found” from a group which we can find in the ThisWorkbook code module
So
Double-click ThisWorkbook to open its code module: then you can write in manually, or better still, evoke from the drop down lists a “Workbook procedure” with the name: Private Sub Workbook_BeforeClose(Cancel As Boolean )
Evoke a Private Sub Workbook_BeforeClose(Cancel As Boolean).JPG : https://imgur.com/T7w6FJN , https://imgur.com/J22uX3g

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' here we can add code to this “already available to us” event coding. Effectively the workbook was Dimed WithEvents somehow to the hidden class or similar of the Workbook object which is effectively also already effectively instantiated as we "open" the Workbook
End Sub I am thinking that in such a code ….. the code has a secret hidden coding at the end which you can’t see. It really looks, pseudo, like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

' here we can add code to this “already available to us” event coding. Effectively the workbook was Dimed WithEvents somehow to the hidden class or similar of the Workbook object which is effectively also already effectively instantiated as we "open" the Workbook

' Secret code section not visible to us
If Cancel = True Then
'Do nothing and Exit this sub
ElseIf Cancel = False Then
'Close this workbook
End If
End SubSo a solution , such as here http://www.eileenslounge.com/viewtopic.php?f=27&t=31331 , is to give you a way to have the default value of Cancel set to True within that code Private Sub Workbook_BeforeClose(Cancel As Boolean ) . ( I assume that the usual default value of Cancel will be False ).
So, as in that post, we add coding in that event procedure which will make Cancel True by default. This will result in the , Private Sub Workbook_BeforeClose(Cancel As Boolean ) preventing a close of the workbook.
For example, assume we have some global Boolean variable, CanClose , which by default will be False , (False is the default state for a Boolean Variable )
Then we add a code line to the event routine:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Let Cancel = Not CanClose ' With CanClose = True, Cancel will be set to Not True = False

' Secret code section not visible to us
If Cancel = True Then
'Do nothing and Exit this sub
ElseIf Cancel = False Then
'Close this workbook
End If
End Sub

The above coding will mean that if we do nothing else, Cancel will be set to True. So in normal use, such as by the user hitting the close button top right , Excel Close Button.JPG: https://imgur.com/ZvQCF2q , that procedure will prevent/cancel the closing.
Then we could make a procedure, Sub CloseMe() , to be put in a normal module , _..

Public CanClose As Boolean ' This will be False by default


Sub CloseMe()
Let CanClose = True
ThisWorkbook.Close
Let CanClose = False ' I don't know why this is here? I don't think it will ever be done!!! ???
End Sub
_... This will allow you to change the value of Cancel to True: It works as follows: If that procedure, Sub CloseMe(), is run, then it changes the global variable, CanClose , to True, and then the next line, _..
ThisWorkbook.Close
_.. cause the code, Private Sub Workbook_BeforeClose(Cancel As Boolean) to start, and with CanClose equal to True, Cancel being set to Not CanClose , will be set to its usual value of False, and the workbook will close as usual.


That all makes sense, I think.
_._________________________________


In the next post, I will do the equivalent with “application events way” in the typical “class / application way” that is typically done. As was discussed in previous posts, this is a slightly less direct way then necessary, but which is preferred for reasons of good programming practice.

[DocAElstein]

( Post 9 )
Part #5B Using “ application events way ” in the typical “class / application way”
Brief description:
As seen in the previous posts, it is very easy to get lost, so it is good to summarise in simple terms what we are going to do:
We use two “standard available event routines” and one “application events routine”
And we build two objects, or rather, we build one new object, and just assign a new variable to an existing object for the second one.
We do a somewhat round about way, more complicated than necessary, as there seems to be some good programming reason for organising things like this in that way.
The standard available event routine which kicks in when the workbook is opened is used. This “builds” the first of two object, in this case, a non standard object from a type (Class). The “blue print” of that type we prepare earlier. This “blue print” information is determined ( written ) inside a Class module which we add. That Class thing has a standard available event routine in it, which is the second standard available event routine thing which we use. That second standard available event routine kicks in when the first object, an object from that type/class is built. We add coding within that event routine to assign an object variable to a second object. That second object is the main object we need. So effectively, the first object being built causes that second object to be built, or rather in this case of the second object, the variable is assigned to an existing object: The variable for that object is declared to the type which is the Excel application itself. In other words, the variable we use for that object is Dimed to the Excel thing we have in front of us. But it is Dimed in a special way, like “Dim WithEvents” . This means that we then have some extra non standard available event coding available to us, which will be the event routines of the type of object ( class) to which we Dim/declare. In this case we Dim/declare to the Excel application itself, and so our new variable has access to the event routines of our Excel application itself.
We choose to use the event routine that “monitors” workbooks being closed. The coding for that we write in the class module. So that becomes part of the second object. So once that object is built/ assigned it has that coding in it. We add in that routine the coding to determine what is done when a workbook is closed.

Full Description and process
Open the VB development window, for example using keys Alt+F8 when you have an Excel Application up and running in front of you.
We insert a new Class module thing: Right click anywhere in the VBA Project window and select to insert a Class module. As we want to declare ( Dim ) to this type, it would be useful to give it a different name.
Right Click in VBA Project window Insert Class module Rename.JPG : https://imgur.com/ZUJGnS4
For example, in line with the reference I am using ( https://stackoverflow.com/questions/46682869/prevent-closing-of-workbook ) I choose the name CloseHelper
In this class code module we need the main ( second ) object which we want , which refers to the open Excel application in front of us. Lets use the variable , ClsLisWb , for this object. It is intended that this object “monitors closing of workbooks.
From the first post of this thread ( http://www.excelfox.com/forum/showthread.php/2294-WithEvents-of-Excel-Application-Events?p=10854&viewfull=1#post10854 ) we know that we do not want to instantiate using Set ClsLisWb = New Excel.Application , as we want to use the existing Excel open in front of us, so we add the single code line , Set ClsLisWb = Excel.Application , to the Initialize routine, which is typically the first event routine which is offered to us from the drop down list in a newly added Class module, Class Initialize Event.JPG : https://imgur.com/CC5XZOB
The object, ClsLisWb , made by the Class initialize code is that which we want to “monitor” closing of workbooks. In the current way of doing things, the Class module effectively has written in it the blue print instructions for the object and sub objects of it, ( in this case ClsLisWb is an under object / sub object “belonging” to the parent object . ( I will arbitrarily name the first object , LisExcelLike ).
I will add here in the Class module an event routine which is now available to ClsLisWb . We look for one which monitors workbook closing and find this one: , Private Sub ClsLisWb_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean) , WorkbookBeforeClose.JPG : https://imgur.com/xVFWMmL
After the object ClsLisWb is “built/assigned”, then this routine will effectively be a method of ClsLisWb which automatically starts on a Workbook close event taking place.
So complete coding such as the following , within the Class module , will partially fulfil our requirements:
CloseHelper.JPG : https://imgur.com/kt46yRn
Class module CloseHelper
Option Explicit
Private WithEvents ClsLisWb As Excel.Application
Private Sub Class_Initialize() ' ' Routine to Instantiate ClsLisWb
Set ClsLisWb = Excel.Application '
End Sub
Private Sub ClsLisWb_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
Let Cancel = Not CunCls ' With CanClose = True, Cancel will be set to Not True = False
If Not Wb Is ThisWorkbook Then Let Cancel = False 'To allow normal closing for other workbooks
' Secret code section not visible to us
If Cancel = True Then
'Do nothing and Exit this sub
ElseIf Cancel = False Then
'Close the workbook, Wb
End If
End Sub
The small additional added coding from us above is similar to that from the last post, except that it includes an extra line so as to restrict the control of the closing to a particular workbook. ( To keep the example similar to the previous post, the closing control is restricted to the workbook in which the code is. ( we did not need that in the previous post ,as the corresponding event routine used , a standard one in that case, only applied to the workbook in which the routine was in. ) ).

So we have the class module part of our solution.
We would typically for convenience include the code to build the object LisExcelLike in the standard available event routine in the ThisWorkbook code module:
ThisWorkbook code module
Private LisExcelLike As CloseHelper
Private Sub Workbook_Open()
Set LisExcelLike = New CloseHelper
End Sub
That code will kick off automatically when the workbook is opened.
Just to remind ourselves again of what goes on: The instantiating of LisExcelLike happens then automatically when the workbook is opened, which in turn results in the instantiating, or rather assignnng, of the main object of interest to us, the second object, ClsLisWb

Finally, as in the previous post example, we have a simple code in a normal code module to allow us to overwrite the close workbook prevention.
Normal Code module
Option Explicit
Public CunCls As Boolean
Sub CloseMe()
Let CunCls = True
ThisWorkbook.Close
Let CunCls = False ' I don't know why this is here? I don't think it will ever be done!!! ???
End Sub







Rem Ref
' ‘ http://www.eileenslounge.com/viewtopic.php?f=27&t=31331
' https://stackoverflow.com/questions/46682869/prevent-closing-of-workbook