Disable Excel Close Button

[monis]

Hello,

I need a help on how I can disable an excel workbook and direct user of my application to use the close button created on the user interface.


waiting for your response

Regards,

Monis

[Doc.AElstein]

Hello Monis,
I copied this solution from here:
https://stackoverflow.com/questions/466 ... f-workbook" onclick="window.open(this.href);return false;
It seems to work for me.
I confess I do not really understand how it works, and I would be very interested if anyone could explain to me how it works.
But this is what it does
If the uploaded file , “StopClosing.xlsm” , is open, then you cannot close any workbook, Including “StopClosing.xlsm” , as long as “StopClosing.xlsm” is open. I think this also prevents you from closing Excel as well.

I only did this little bit, along with a small modification to the codes from that stackoverflow forum link :
In a normal code module i wrote the small code below.
Also in that normal code module I have a public global variable , SchallEyeClose
Public SchallEyeClose As Boolean
That variable will be False , usually , by default.
If you changed that variable to True, ( for example from within your UserForm after the user used the close on your user form) , then you will not be prevented from closing any workbook or Excel


As example, open the uploaded file, “StopClosing.xlsm” ( and enable macros )
Try closing it – you shouldn’t be able to, that should be prevented.
If you then run this code,

Code: Select all

Sub LetClosing() ' If you run this code, then you can close stuff
 Let SchallEyeClose = True
End Sub

_.. then after you should be able to close the file normally, which means it would not be prevented from being closed.

So to use this solution, you will need to make a class module in your file, and give it the name "CloseHelper" , and copy all the codes to your file.
Then, you could, for example, in your user form code that runs after the user hits the user form close thing, include a code line like
Call LetClosing )
Alan

_._____________________
Here the codes to go in class module, CloseHelper

Code: Select all

Option Explicit
Private WithEvents m_App As Excel.Application
Private Sub Class_Initialize()
 Set m_App = Excel.Application
End Sub
Private Sub Class_Terminate()
 Set m_App = Nothing
End Sub
Private Sub m_App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    If SchallEyeClose = True Then
     Let Cancel = False
    Else
     Let Cancel = True
     MsgBox prompt:="Hello from m_App_WorkbookBeforeClose" & vbCrLf & "Please use the userform button to close stuff"
    End If
End Sub

This below must go in any normal code module:

Code: Select all

Public SchallEyeClose As Boolean
Sub LetClosing() ' If you run this code, then you can close stuff
 Let SchallEyeClose = True
End Sub

This must go in the Thisworkbook code module

Code: Select all

Option Explicit
Private m_CloseHelper As CloseHelper
Private Sub Workbook_Open()
 Set m_CloseHelper = New CloseHelper
End Sub

_._______________________
P.s. If I ever figure out how it works I will let you know… the only bit I did and understand is the stuff with the variable SchallEyeClose. That basically changes a variable, Cancel , which is some sort of variable that is always there somewhere and somehow determines if you can close stuff... somehow...

[HansV]

That code (with a class module) is needed when you want to prevent the user from closingANY workbook.
If Monis only wants to prevent the user from closing one specific workbook (the workbook containing the code), it is simpler. No class module needed.

1) In a standard module:

Code: Select all

Public CanClose As Boolean

Sub CloseMe()
    CanClose = True
    ThisWorkbook.Close
    CanClose = False
End Sub

The macro CloseMe can be assigned to a command button, for example.

2) In the ThisWorkbook module:

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = Not CanClose
End Sub

[monis]

Thanks HansV,

You understand my question very well, I actually want to restrict user on only a particular workbook were I have an application built for my small business in manufacturing that I use in tracking our process.

I am not do good in codes I want to understand were to place this codes so as to hello me restrict user from using the excel "X" button to close the application.

I attached a screenshot of the close button

[HansV]

Press Alt+F11 to activate the Visual Basic Editor.
Select Insert > Module.
Copy the following code into the module that appears:

Code: Select all

Public CanClose As Boolean

Sub CloseMe()
    CanClose = True
    ThisWorkbook.Close
    CanClose = False
End Sub

On the left hand side, you should see the Project Explorer pane (it resembles the folder pane of Windows Explorer).
Under 'Microsoft Excel Objects', you should see the worksheets plus an item named ThisWorkbook.
Double-click ThisWorkbook to open its code module.
Copy the following code into ThisWorkbook:

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = Not CanClose
End Sub

Switch back to Excel.
Right-click the 'CLICK TO CLOSE APPLICATION' button.
If you see a context menu, select Assign Macro... from it. (If you don't see a context menu, let me know).
Select the CloseMe macro from the list, then click OK.

[PJ_in_FL]

Monis,

See the post I made recently in the VBA forum on how to use Excel as an application.

Setting your workbook and userform up in this way and using VBS to open the workbook as an application will allow the workbook to function as an independent application, without interfering with other Excel workbooks that are open.

[monis]

Hello Hansv,

I right click the "CLICK TO CLOSE APPLICATION" button but i was not able to see the context menu.

Please could you help me to check what the issue was.

waiting to read from you.


Thanks,

Regards,

Monis

[HansV]

That means you are using an ActiveX button.
Do the following:
- Activate the Developer tab of the ribbon.
- Click the 'Design Mode' button in the Controls group. It should become highlighted.
- Double-click your 'CLICK TO CLOSE APPLICATION' button.
- This will take you to the event procedure in the Visual Basic Editor.
- It will look similar to this (but the name will probably be different):

Code: Select all

Private Sub CommandButton1_Click()

End Sub

- Make it look like this:

Code: Select all

Private Sub CommandButton1_Click()
    CloseMe
End Sub

- Switch back to Excel.
- Click the 'Design Mode' button to remove the highlight.

[monis]

Dear Hans

Thanks so much. it work perfectly.


Regards,

Monis

[Doc.AElstein]

If I ever figure out how it works I will let you know… ..

This is just a follow up out of passing interest…
I was interested in understanding the codes from this Thread. I finally managed it...
I made myself some notes , and also shared them here: http://www.excelfox.com/forum/showthrea ... ion-Events" onclick="window.open(this.href);return false;
The solution type like from Hans I explain here http://www.excelfox.com/forum/showthrea ... #post10859" onclick="window.open(this.href);return false;
The solution type like I gave here took me quite a while to understand. I did a full explanation here: http://www.excelfox.com/forum/showthrea ... #post10860" onclick="window.open(this.href);return false;
I will give just a very brief simplified explanation of the solution which I posted here, as I said I would when I posted my solution…

Solutions like the one I gave look a lot more complicated than they are and are particularly confusing to someone new to VBA just because of the somewhat round about way that programmers seem to like to do these sort of things.

Two “standard available event routines” and one “application events routine” are used.
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.

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.

The end effect is similar in both solutions.
In plain English: Effectively we have added extra coding into the existing Excel coding which runs when something is closed. That coding comes into existance when the second object is made, which is more or less when the workbook is opened


Alan