Disable the "X" close button in an excel application

[Tempy]

Hi all,

I have written some code that automatically hides all tool bars on
opening. I then have an exit button that takes it to some more code that
shows the tool bars again. However some people have closed the workbook
by just clicking on the "X" close button which does not repair the
workbook and when they re-open exel the tool bars are not there !!
Is it possible to either hide or deactivate the "X" button ?

Thanks for all the help on my last queries

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

 

[Patrick Molloy]

You can trap an application level event for this

Here's an example XLA

1) start a new workbook.
2) add a class module,name it clXL
3) in the class module code page addthis

Option Explicit
Private WithEvents xl As Excel.Application
Private Sub xl_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel
As Boolean)
Cancel = _
(MsgBox("OK", vbYesNo, "Closing " & Wb.Name) = vbNo)
End Sub
Private Sub Class_Initialize()
Set xl = Excel.Application
End Sub

4) add a standard code module
5) in the code sheet of the standard code module add this code:
Option Explicit
Public xl As clXL
Sub Autpen()
Set xl = New clXL
End Sub

6) save the workbook as an XLA, remember where as we'll open it again soon!

close excel
Open excel.
with the add-in manager open the XLA...browse to it if need be

When the add-in opens, the auto_open sub creates the variable set to the
open instance of excel.
Closing a workbook or excel itself will raise a message.

This is a simple example to demonstrate how simple it is to use application
level events.

 

[keepITcool]

Patrick

I can't seem to get this across to 'the establishment'

it is a COMPLEX example of how to create an application level
event handler.

THIS is a simple example.:

'Code in workbook object module
Option Explicit
Dim WithEvents xlApp As Application

Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)
Cancel = MsgBox("OK", vbYesNo, "Closing " & Wb.Name) = vbNo
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Patrick Molloy wrote :

 

[keepITcool]

You could cancel the close as Patrick suggested..

however following will just Dis/Enable the system menu on the window
Note that if you plan to use it on xl97:
you'll need to change application.hwnd with a FindWindow call



Option Explicit
Private Declare Function GetWindowLong Lib "user32.dll" Alias
"GetWindowLongA" ( _
ByVal hwnd As Long, _
ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32.dll" Alias
"SetWindowLongA" ( _
ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long

Sub DisableX()
With Application
SetWindowLong .hwnd, -16, GetWindowLong(.hwnd, -16) And Not &H80000
End With
End Sub
Sub EnableX()
With Application
SetWindowLong .hwnd, -16, GetWindowLong(.hwnd, -16) Or &H80000
End With
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tempy wrote :

 

[Bob Phillips]

Ah well, in for a penny, in for a pound.

Even though I may think that this is a better way of setting application
level events (see my discussion with Dave Peterson some 4 weeks ago), your
example is no less simple, it just saves on one class module. The complexity
of the solution is the same. Surely, even you would not say that 100 lines
of code is necessarily more complex than 50 lines.

Bob

 

[Tempy]

Hi to both of you,

Thanks for the help, but i cannot get it to work....I fairly new to this
game and i have probably worded it incorrectly !!

It is an excel workboox that i am using, is that the same as an
application ?

Where should i put this code after the autpen macro ?

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

 

[keepITcool]

as clearly stated in my post:

the code should go the workbook's object module.
(the code behind Thisworkbook)

during testing you may have to run the workbook_open proc
manually to (re)set the xlApp variable resp Patrick's class.

Also see my alternative (posted as a direct answer to your question
in the root of this thread) which maybe more to your liking

Since you are new:
dont spend too much time taking control away from the user.
hiding toolbars and even disabling the ability to close excel
requires your code to be absolutely bulletproof.
Else buy a bulletproof vest, cuz your users will come after you.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tempy wrote :

 

[Tempy]

Thanks keepITcool,

I appreciate your help.


Tempy

*** Sent via Developersdex http://www.developersdex.com ***

 

[keepITcool]

I'm avoiding an entire class module.. thus

iso working with 2 objects (cls in thisworkbook and app in cls)
i'm working with 1 object (app in thisworkbook)

'no less simple' to those who are unfamiliar with class modules?
hmm... try more transparent <g>


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

 

[Bob Phillips]

I'm avoiding an entire class module.. thus

iso working with 2 objects (cls in thisworkbook and app in cls)
i'm working with 1 object (app in thisworkbook)

'no less simple' to those who are unfamiliar with class modules?

Those unfamilar with class modules will probably be just as unfamiliar that
Thisworkbook is a class module, and with application events. In most
instances, they will want a solution, and bullet proof instructions to
implement it. Accusing Patrick and 'the estrablishment' (whatever that is)
of being unable to comprehend this seeming (to you) axiomatic truth does not
help the poster in any way. Patrick's solution achieves the same result as
yours.

Your point is a good point (IMO), but you cloud it with the way it is
raised.

hmm... try more transparent <g>

Sorry, I am not being obtuse (really <g>), but it is no more transparent
either. It is better, IMO as well as yours, as it saves on that unnecessary
class module, but the complexity factor is still there, the application
events voodoo is still there. Some even think the extra class module is
better, as I found when I madethis case some weeks ago.

 

[Jan Karel Pieterse]

Hi Bob,

It is better, IMO as well as yours, as it saves on that unnecessary
class module,

I must say I disagree on that point.

Class modules can easily be cleaned by an export/remove/import cycle
(or by using Rob Bovey's code cleaner), but the Thisworkbook module
cannot be removed.

So when a bloat creeps into the Thisworkbook module, one will have to
start with a fresh workbook to remove it.

This my main reason why I try to keep the Thisworkbook module as slim
as I possibly can.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[keepITcool]

I've had similar discussions in the past, but sofar nobody explained me
why separate class would be better.

I had no luck trying to find that thread you mentioned.
Do you remember it's topic?

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

 

[Bob Phillips]

Well Jan Karel has just given one reason (which again I can't say I ascribe
to), and the thread in question can be found at http://tinyurl.com/8ndb9.
You will probably smile at the reason, but as he says, it is just a personal
choice.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Patrick Molloy]

hmmmm

Well I won't be drawn into debates. Especially when you're comments are
unnecessarily rude.

However.
You clearly mis-understood the intention of my code. It was aimed at giving
some instruction as to how to raise application level events. Of course
there atre many ways to skin a cat...and I could as easily have writtem this
in C#.
The code given was clear and concise. It would have enabled somebody who
hadn't seen this kind of code soke ideas. It may have offered a solution to
other readers too.

 

[keepITcool]

Patrick,

Reading back I can see my phrasing was blunt. I never intended to be
rude. Apologies.

Isn't a discussion about the merits /demerits of a certain approach
more interesting than the tons of Q&A about 'runtime error 1004'?

Dont think of it as 'drawn into a debate', but sharing your knowledge
and experience. <g>

Are there any disadvantages to using thisworkbook object module as the
container of application event code?



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Patrick Molloy wrote :

 

[keepITcool]

OFF TOPIC?

Hi Jan Karel,

I've never knew you could have bloat in thisworkbook..

I honestly thought codebloat could only exist in userforms, where the
compiled parts (the frx) contains dirt from unused objects.
(I've also had some troublesome experiences with FlexGrids, which have
some nasty habits)

I can imagine the flexgrid problems popping up in sheets, but then that
would bloat the sheet object being the container of the troublesome
activeX... and i doubt the problems would be solved by exporting/
importing the code as that wouldnt affect the sheet's objects.
(I'll test that)

Codebloat in normal or class modules theoretically doesnt make sense.
(note that some filereduction from CodeCleaner is achieved simply by
saving it in decompiled state)

You wouldn't have an example of bloated code (in a plain or workbook
module) somewhere on your harddisk, would you?

I'd love to have a look.


--
Jurgen
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jan Karel Pieterse wrote :

 

[Jan Karel Pieterse]

Hi KeepITcool,

Codebloat in normal or class modules theoretically doesnt make sense.
(note that some filereduction from CodeCleaner is achieved simply by
saving it in decompiled state)

It happens though. I have seen a couple of workbooks of mine go
haywire, which mostly could be fixed by a code cleaning job.
Mostly it requires Modules that contain quite some code (50K or more)
and have been heavily edited in the past without ever cleaning up.

One was even corrupted so bad I had to copy the code to Notepad and
paste that back after inserting a new module.

The symptom it showed was that I would get a compile error (object
doesn't support this property or method!) on this kind of line:

sTemp=Thisworkbook.worksheets("Sheet1").Range("A1").Value

Note the lack of capitalisation of the worksheets collection!

You wouldn't have an example of bloated code (in a plain or workbook
module) somewhere on your harddisk, would you?

Sorry, this was a customer workbook I cannot share.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[Stephen Bullen]

Hi KeepITcool,

Are there any disadvantages to using thisworkbook object module as the
container of application event code?

To me, it comes down to encapsulation - having one module do one thing
and do it well, so I can copy it around and reuse it in other projects
almost unchanged. So having one class that handles application-level
events and only handles application-level events makes more sense to me
than having a class that contains a mixture of book-level and app-level
events.

On the other hand, if I think functionally rather than in terms of
'levels', I could easily imagine a class that does a specific
(high-level) 'job', but to do that, it needs to respond to both
app-level and book-level events.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[keepITcool]

Hi Stephen,

valid reasoning <g>.
thx for sharing your thoughts.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Stephen Bullen wrote :

 

[Tempy]

Hi keepITcool,

I have Inserted your code into my object module but the sentance, "Dim
WithEvents xlApp As Application", keeps giving me an error.

Could you possibly help ?

Thanks

Tempy

*** Sent via Developersdex http://www.developersdex.com ***