Class Stuff: VBA Custom Classes & Objects, Class Modules

**DocAElstein** · 12-26-2018, 04:35 PM

**DocAElstein** · 12-26-2018, 04:35 PM

**DocAElstein** · 12-26-2018, 04:35 PM

Not part of this blog - blog on class stuff to be finished next winter
Alan. April 2020

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.

**DocAElstein** · 12-26-2018, 04:35 PM

Not part of this blog - blog on class stuff to be finished next winter
Alan. April 2020

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.

**DocAElstein** · 12-26-2018, 04:35 PM

Not part of this blog - blog on class stuff to be finished next winter
Alan. April 2020

( Post 7 )
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/...ng-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 ( jgzggtjgjhgjgjg ) 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

Code:

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

Code:

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

Code:

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/...ng-of-workbook

**DocAElstein** · 12-26-2018, 04:35 PM

Not part of this blog - blog on class stuff to be finished next winter
Alan. April 2020

( Post 7 )
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/...ng-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 ( jgzggtjgjhgjgjg ) 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

Code:

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

Code:

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

Code:

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/...ng-of-workbook

**DocAElstein** · 12-26-2018, 04:35 PM

Not part of this blog - blog on class stuff to be finished next winter
Alan. April 2020

( Post 7 )
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/...ng-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 ( jgzggtjgjhgjgjg ) 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

Code:

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

Code:

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

Code:

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/...ng-of-workbook

**DocAElstein** · 12-26-2018, 04:35 PM

Not part of this blog - blog on class stuff to be finished next winter
Alan. April 2020

( Post 7 )
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/...ng-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 ( jgzggtjgjhgjgjg ) 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

Code:

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

Code:

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

Code:

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