WithEvents of Excel.Application Events
(This is post 2693. Its a spare copy, made in Jan 2021)
WithEvents of Excel.Application
Post #1 Introduction
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 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...._
Code:
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
_.... in a worksheet code module : Right mouse click Or double click in VBA explorer Project window to get code module.JPG : https://imgur.com/gsz6s2N
That code gives you a simple message if you change the value in the first worksheet cell :
Automatic message after change value in first cell .JPG : https://imgur.com/WFINlbq ( In this routine I am using Me for convenience as, in simple terms, it will be referring to the worksheet.)
Coding of the above nature may sometimes be referred to loosely as “normal event codes” or “standard available event codes”
This tutorial is intended to explain what is going on in such coding in a bit more detail which allows you make more full use of event type things.
In my opinion, like many things involved in Excel and Excel VBA, the events subject is made more difficult to understand, and takes a lot longer in the long run, by failing to take a bit more time initially to understand fully what it is about. So this thread Tutorial tries to start at the beginning, which is not really much further up than the “standard available event codes”. It tends to look more advanced than it is , partly just because of how things have a habit of being organised for the sake of good programming practice.. This organisation means that the learner often comes across other topics for the first time which are not directly needed, but may appear so..
Event coding generally is just part of the general VBA stuff which is itself all about allowing us to use ourselves some of the coding used internally which we don’t see which is / was used by Microsoft to make Excel work.
Review of some relevant basic programming concepts
We must refresh some basic ideas of we will get lost very quickly.
Object Orientated Programming, Excel object and excel objects
Excel Application object, Dim As
You will be familiar with “opening” your Excel or “getting Excel up” in front of you. If you understand a bit about Object Orientated Programming, OOP, then you will know that it is all about Objects, and an object can be a whole lot of diverse things. OOP is an annoyingly vague concept. The Objects tend to be organised hierarchically starting with the big ones at the top of the hierarchy, and going down through smaller ones. ( https://imgur.com/44omYFi , http://www.excelfox.com/forum/showth...ange#post10809 ) The “Excel in front of you” is also an object,: a “very big” one! You could even have a second Excel object by “opening” the Excel of another computer or opening Excel again yourself on the original computer.
This can be demonstrated by running this simple code, as you can also get your second Excel object using code from within your first Excel , like in Rem 1 in the following code:
Code in second worksheet code module:
Code:
Sub GetingAtExcel()
Rem 1 Get another independant (clone of you on) Excel up in front of you
Dim ExcelApp2 As Object ' just about anything can be an object ..._
Set ExcelApp2 = New Excel.Application ' Get another independant Excel up in front of you _.. this object is like another "you sitting on the computer usiong Excel"!!!
Let ExcelApp2.Visible = True ' The first you is visible, but by default, an object of a clone of you is invisible. This line will make your clone visible
Set ExcelApp2 = Nothing ' This is similar to "closing" the Excel, in this case the second "you sitting using Excel"
Rem 2 Normal stuff using code in this workbook
Let ThisWorkbook.Worksheets.Item(1).Name = "Shite1"
Rem 3 Do stuff in this workbook using a variable for Excel
Dim ExcelApp As Object
Set ExcelApp = Excel.Application ' This will default to the Excel in which this code is in. The variable will reference ( point ) to the object of the current Excel "open" in front of you
Let ExcelApp.Workbooks(ThisWorkbook.Name).Worksheets.Item(1).Name = "Sht_1"
Set ExcelApp = Nothing ' This is debateably good or unecerssary practice to "empty" the variable. It wont "close" Excel
End Sub
Similarly you can use a variable as in Rem 3 to get at the first Excel object.
( The word Application or App seems to excite sexually computer people so they use it a lot wildly and vaguely. For our considerations it is probably referring mostly to one of these “Excel”s that you “have” at any time )
Objects and class objects
Excel Application object , Dim WithEvents As
Before we take this further we must try to get another further “feel” for the somewhat imprecise , vague and abstract concept of the objects in VBA and Object Orientated Programming, at least as far as it concerns Event things.
As a general rule we need to accept a couple of imprecise/ vague concepts:
_ Events and Event codes
_ most of what happens in Excel is said to happen to an object through events. Some of this coding is available to us. There is a syntax to coding of this sort which is something like this
__Sub TheObjectToWhichTheCodingApplies_TheEventType(_____________)
We can see the direct comparison to the code example at the start of this post.
____Sub _ Worksheet__Change(ByVal Target As Range)
The first part, the object, you can choose. The single underscore, _ , followed by a word is one of many reserved _EventType character combinations. You must choose one which is available to the object that you choose. ( Some objects have available by default certain events, that is to say, or rather it is said then , to be “subscribed” to certain events.
At the declaring ( Diming ) stage you can “subscribe an object” to all the events of an objects type ( Class ). This is done by Diming in a slightly different way: Instead of like:.._
Dim Ws As Worksheet
_.. to “subscribe” Ws to the events of the Worksheet type Class we do this:
Dim WithEvents Ws As Worksheet
( We will see that things are not always straight forward, and , for example, Diming WithEvents to some things, such as a Worksheet is not particularly a good idea…… )
_ The actual object: where what how to get at or change
_ what precisely/ physically any object is, is not precisely defined. Consequently what we actually use, and where, in order to “use” an object is somewhat abstract and can be different at different times or for different purposes. As example, In the code example above I was using the second worksheet in a workbook, ( https://imgur.com/gsz6s2N ) That worksheet object could “physically” be described as the spreadsheet we “see” when clicking on the second tab. Writing into cells could be described as using the worksheet object. But you will see that in the simple routine above, we referred to the second worksheet object using “.Me” . This is because the code module and code window shown in the screenshots above is also often considered to be that worksheet object. This should confuse you. The concept is not precise. I think possibly in the last 20 years there were too many people employed in the computer industry who had nothing to do. They may have gone a bit mad in their boredom.
At this point it is useful to review again some of the vague concepts associated with Object Orientated Programming. This is mainly because the “habits” of where some programmers choose to place their coding, which can unnecessarily complicate the situation. To try and avoid further clutter here, I will place this review here:
http://www.excelfox.com/forum/showth...0838#post10838
If you are fully familiar with the use of objects and Classes in VBA , then you can skip that review.
The main reason for the recap on class and object concepts is to attempt to offset confusion in the next post: Without a basic understanding of those concepts, it is easy to read more into the subject of Events than is actually there: This is because decisions on where codes are kept and how they are organised are often based on convenience, personal preferences, and on an attempt to fit in to concepts which are vague. One result of this is that an inexperienced programmer may come across some aspects of Class object module coding for the first time when considering Event codes and miss read that as being a significant part of the Event coding.
Ref
https://www.youtube.com/watch?v=SaQfOIeOuHk
https://www.youtube.com/watch?v=SaQf...U2DGkqV4AaABAg
WithEvents of Excel.Application. Encapsturbation Encapsulation
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/showth...ge10#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!!!….
_ We do not need to do the following to step through a code placed in such a module via F8:
Tools Options.JPG : https://imgur.com/KZUyPnB
Break in class modules.JPG : https://imgur.com/75Eg6UE
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. 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: https://imgur.com/vq9gQ9u , WsTyp2 Events.JPG : https://imgur.com/GWSNDS9
Code:
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
Event coding in Excel spreadsheets(Application Events) Misc
Post 3: Event coding in Excel spreadsheets. Application Events
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 : https://imgur.com/gsz6s2N
Code:
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 : https://imgur.com/kyE0qKh
Code:
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:
Code:
Sub WsMe_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = Me.Name Then
Else '
End If
End Sub
The above could be considered as what is behind the following in a worksheet code module, and so is a direct equivalent to it:
Code:
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 …._
Code:
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 )
Code:
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:…._
Code:
Sub WsSht_1_SheetChange(ByVal Sh As Object, ByVal Target As Range)
End Sub
_... we added coding so that it looks now like this:_......
Code:
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
Disable Excel Close Button/ Control closing
Further working examples
Disable Excel Close Button/ Control closing
( Post 6 )
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
Code:
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:
Code:
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 Sub
So 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:
Code:
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 , _..
Code:
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.
