Hi All
Is there a way of disabling the Windows X - application close button - after
opening Excel. I know I can do this with a userform but not the application
as a whole.

Thanks

Nigel

It is rarely a good idea to modify thebehaviour of a standard interface

If you want to disallow the X from closing the form, use this

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer
Cancel = Tru
End Su

to the best of my knowledge you can't prevent the user
closing the application by disabling the 'x'
However, if there's an workbook open, by preventing its
closing you'd force exel to stay open.

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi All
Is there a way of disabling the Windows X - application
close button - after
opening Excel. I know I can do this with a userform but
not the application
as a whole.

Thanks

Nigel


.

Hi Nigel;
Try this (in ThisWorkbook module):

Private Declare Function FindWindow& Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String)
Private Declare Function GetSystemMenu& Lib "user32" _
(ByVal hWnd As Long, ByVal bRevert As Long)
Private Declare Function DeleteMenu& Lib "user32" (ByVal hMenu As Long _
, ByVal nPosition As Long, ByVal wFlags As Long)
Private Declare Function DrawMenuBar& Lib "user32" (ByVal hWnd As Long)
Private hWnd&

Private Sub Workbook_Activate()
DeleteMenu GetSystemMenu(hWnd, 0), &HF060, 0&
DrawMenuBar hWnd
End Sub

Private Sub Workbook_Deactivate()
GetSystemMenu hWnd, True
DrawMenuBar Application.hWnd
End Sub

Private Sub Workbook_Open()
hWnd = FindWindow(vbNullString, Application.Caption)
End Sub

MP

"Nigel" a écrit dans le message de
...
Hi All
Is there a way of disabling the Windows X - application close button - after
opening Excel. I know I can do this with a userform but not the application
as a whole.

Thanks

Nigel

Have a look at

http://www.vbdesign.net/expresso/arc...opic/2737.html

That is for a form, not windows.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"AA2e72E" wrote in message
...
Have a look at:

http://www.vbdesign.net/expresso/arc...opic/2737.html

Michael,

Small error. In Workbook_Deactivate it should be

DrawMenuBar hWnd

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Michel Pierron" wrote in message
...
Hi Nigel;
Try this (in ThisWorkbook module):

Private Declare Function FindWindow& Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String)
Private Declare Function GetSystemMenu& Lib "user32" _
(ByVal hWnd As Long, ByVal bRevert As Long)
Private Declare Function DeleteMenu& Lib "user32" (ByVal hMenu As Long _
, ByVal nPosition As Long, ByVal wFlags As Long)
Private Declare Function DrawMenuBar& Lib "user32" (ByVal hWnd As Long)
Private hWnd&

Private Sub Workbook_Activate()
DeleteMenu GetSystemMenu(hWnd, 0), &HF060, 0&
DrawMenuBar hWnd
End Sub

Private Sub Workbook_Deactivate()
GetSystemMenu hWnd, True
DrawMenuBar Application.hWnd
End Sub

Private Sub Workbook_Open()
hWnd = FindWindow(vbNullString, Application.Caption)
End Sub

MP

"Nigel" a écrit dans le message de
...
Hi All
Is there a way of disabling the Windows X - application close button -
after
opening Excel. I know I can do this with a userform but not the
application
as a whole.

Thanks

Nigel

"Bob Phillips" wrote in message
...
Michael,

Small error. In Workbook_Deactivate it should be

Hi Bob,

Depends on which versions of Excel you're targeting. The Application
object in Excel 2002 and higher has an Hwnd property, In which case the
FindWindow API call in this example is unnecessary.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Thanks for the info Rob.

I have XL2000, and it didn't work for me. It's about time that Applictaion
had an hWnd property, good to hear that. Are there any others?

Bob

"Rob Bovey" wrote in message
...
"Bob Phillips" wrote in message
...
Michael,

Small error. In Workbook_Deactivate it should be

Hi Bob,

Depends on which versions of Excel you're targeting. The Application
object in Excel 2002 and higher has an Hwnd property, In which case the
FindWindow API call in this example is unnecessary.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Hi Bob;
You are right Bob; the explanation was given by Rob.
Application.Hwnd is only for the lucky.
:-) MP

"Bob Phillips" a écrit dans le message de
...
Michael,

Small error. In Workbook_Deactivate it should be

DrawMenuBar hWnd

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Michel Pierron" wrote in message
...
Hi Nigel;
Try this (in ThisWorkbook module):

Private Declare Function FindWindow& Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String)
Private Declare Function GetSystemMenu& Lib "user32" _
(ByVal hWnd As Long, ByVal bRevert As Long)
Private Declare Function DeleteMenu& Lib "user32" (ByVal hMenu As Long _
, ByVal nPosition As Long, ByVal wFlags As Long)
Private Declare Function DrawMenuBar& Lib "user32" (ByVal hWnd As Long)
Private hWnd&

Private Sub Workbook_Activate()
DeleteMenu GetSystemMenu(hWnd, 0), &HF060, 0&
DrawMenuBar hWnd
End Sub

Private Sub Workbook_Deactivate()
GetSystemMenu hWnd, True
DrawMenuBar Application.hWnd
End Sub

Private Sub Workbook_Open()
hWnd = FindWindow(vbNullString, Application.Caption)
End Sub

MP

"Nigel" a écrit dans le message de
...
Hi All
Is there a way of disabling the Windows X - application close button -
after
opening Excel. I know I can do this with a userform but not the
application
as a whole.

Thanks

Nigel

Hi Bob,

There were quite a few new features in 2002, the most interesting of
which (at least to me) was the new Protection object that allowed you much
more granular control over what the user could and could not do on a
protected worksheet. It's significantly better than the all or nothing
approach in Excel 2000 and earlier.

Maybe one of these days I'll even find a client that's upgraded all
their computers to Excel 2002 or higher so I can actually build a real
application that uses it. <g Right now I'm stuck in Excel 2000 backward
compatibility mode for the foreseeable future.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Bob Phillips" wrote in message
...
Thanks for the info Rob.

I have XL2000, and it didn't work for me. It's about time that Applictaion
had an hWnd property, good to hear that. Are there any others?

Bob

"Rob Bovey" wrote in message
...
"Bob Phillips" wrote in message
...
Michael,

Small error. In Workbook_Deactivate it should be

Hi Bob,

Depends on which versions of Excel you're targeting. The Application
object in Excel 2002 and higher has an Hwnd property, In which case the
FindWindow API call in this example is unnecessary.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Thanks again Rob.

I think I also remember someone using an xlPasteFormatAndFormulas (or
something akin to that) in PasteSpecial , although that is no big deal as
you can do it already, albeit in 2 steps.

Bob

"Rob Bovey" wrote in message
...
Hi Bob,

There were quite a few new features in 2002, the most interesting of
which (at least to me) was the new Protection object that allowed you much
more granular control over what the user could and could not do on a
protected worksheet. It's significantly better than the all or nothing
approach in Excel 2000 and earlier.

Maybe one of these days I'll even find a client that's upgraded all
their computers to Excel 2002 or higher so I can actually build a real
application that uses it. <g Right now I'm stuck in Excel 2000 backward
compatibility mode for the foreseeable future.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Bob Phillips" wrote in message
...
Thanks for the info Rob.

I have XL2000, and it didn't work for me. It's about time that
Applictaion
had an hWnd property, good to hear that. Are there any others?

Bob

"Rob Bovey" wrote in message
...
"Bob Phillips" wrote in message
...
Michael,

Small error. In Workbook_Deactivate it should be

Hi Bob,

Depends on which versions of Excel you're targeting. The
Application
object in Excel 2002 and higher has an Hwnd property, In which case
the
FindWindow API call in this example is unnecessary.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Thanks Michel, this works in my Excel 2002 environment, do you know if it
will in Excel97 ?
I do not understand all the intricacies of the subsequent hWnd debate.

Also will this work in Outlook (2002 and 98) ?

Cheers
Nigel

"Michel Pierron" wrote in message
...
Hi Nigel;
Try this (in ThisWorkbook module):

Private Declare Function FindWindow& Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String)
Private Declare Function GetSystemMenu& Lib "user32" _
(ByVal hWnd As Long, ByVal bRevert As Long)
Private Declare Function DeleteMenu& Lib "user32" (ByVal hMenu As Long _
, ByVal nPosition As Long, ByVal wFlags As Long)
Private Declare Function DrawMenuBar& Lib "user32" (ByVal hWnd As Long)
Private hWnd&

Private Sub Workbook_Activate()
DeleteMenu GetSystemMenu(hWnd, 0), &HF060, 0&
DrawMenuBar hWnd
End Sub

Private Sub Workbook_Deactivate()
GetSystemMenu hWnd, True
DrawMenuBar Application.hWnd
End Sub

Private Sub Workbook_Open()
hWnd = FindWindow(vbNullString, Application.Caption)
End Sub

MP

"Nigel" a écrit dans le message de
...
Hi All
Is there a way of disabling the Windows X - application close button -
after
opening Excel. I know I can do this with a userform but not the
application
as a whole.

Thanks

Nigel

It should work okay in XL97 as long as you note the comment that I made.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Nigel" wrote in message
...
Thanks Michel, this works in my Excel 2002 environment, do you know if it
will in Excel97 ?
I do not understand all the intricacies of the subsequent hWnd debate.

Also will this work in Outlook (2002 and 98) ?

Cheers
Nigel

"Michel Pierron" wrote in message
...
Hi Nigel;
Try this (in ThisWorkbook module):

Private Declare Function FindWindow& Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String)
Private Declare Function GetSystemMenu& Lib "user32" _
(ByVal hWnd As Long, ByVal bRevert As Long)
Private Declare Function DeleteMenu& Lib "user32" (ByVal hMenu As Long _
, ByVal nPosition As Long, ByVal wFlags As Long)
Private Declare Function DrawMenuBar& Lib "user32" (ByVal hWnd As Long)
Private hWnd&

Private Sub Workbook_Activate()
DeleteMenu GetSystemMenu(hWnd, 0), &HF060, 0&
DrawMenuBar hWnd
End Sub

Private Sub Workbook_Deactivate()
GetSystemMenu hWnd, True
DrawMenuBar Application.hWnd
End Sub

Private Sub Workbook_Open()
hWnd = FindWindow(vbNullString, Application.Caption)
End Sub

MP

"Nigel" a écrit dans le message de
...
Hi All
Is there a way of disabling the Windows X - application close button -
after
opening Excel. I know I can do this with a userform but not the
application
as a whole.

Thanks

Nigel

Thanks Bob.

Any ideas about converting it for use in Outlook?

Cheers
Nigel

"Bob Phillips" wrote in message
...
It should work okay in XL97 as long as you note the comment that I made.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Nigel" wrote in message
...
Thanks Michel, this works in my Excel 2002 environment, do you know if
it
will in Excel97 ?
I do not understand all the intricacies of the subsequent hWnd debate.

Also will this work in Outlook (2002 and 98) ?

Cheers
Nigel

"Michel Pierron" wrote in message
...
Hi Nigel;
Try this (in ThisWorkbook module):

Private Declare Function FindWindow& Lib "user32" Alias "FindWindowA"
_
(ByVal lpClassName As String, ByVal lpWindowName As String)
Private Declare Function GetSystemMenu& Lib "user32" _
(ByVal hWnd As Long, ByVal bRevert As Long)
Private Declare Function DeleteMenu& Lib "user32" (ByVal hMenu As Long
_
, ByVal nPosition As Long, ByVal wFlags As Long)
Private Declare Function DrawMenuBar& Lib "user32" (ByVal hWnd As
Long)
Private hWnd&

Private Sub Workbook_Activate()
DeleteMenu GetSystemMenu(hWnd, 0), &HF060, 0&
DrawMenuBar hWnd
End Sub

Private Sub Workbook_Deactivate()
GetSystemMenu hWnd, True
DrawMenuBar Application.hWnd
End Sub

Private Sub Workbook_Open()
hWnd = FindWindow(vbNullString, Application.Caption)
End Sub

MP

"Nigel" a écrit dans le message de
...
Hi All
Is there a way of disabling the Windows X - application close
button -
after
opening Excel. I know I can do this with a userform but not the
application
as a whole.

Thanks

Nigel

Nigel,

It currently is triggered when a particular workbook is activated. What
would be the Outlook trigger, that is what so you want to tie it to, and
when would it be invoked?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Nigel" wrote in message
...
Thanks Bob.

Any ideas about converting it for use in Outlook?

Cheers
Nigel

"Bob Phillips" wrote in message
...
It should work okay in XL97 as long as you note the comment that I made.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Nigel" wrote in message
...
Thanks Michel, this works in my Excel 2002 environment, do you know if
it
will in Excel97 ?
I do not understand all the intricacies of the subsequent hWnd debate.

Also will this work in Outlook (2002 and 98) ?

Cheers
Nigel

"Michel Pierron" wrote in message
...
Hi Nigel;
Try this (in ThisWorkbook module):

Private Declare Function FindWindow& Lib "user32" Alias
"FindWindowA"
_
(ByVal lpClassName As String, ByVal lpWindowName As String)
Private Declare Function GetSystemMenu& Lib "user32" _
(ByVal hWnd As Long, ByVal bRevert As Long)
Private Declare Function DeleteMenu& Lib "user32" (ByVal hMenu As
Long
_
, ByVal nPosition As Long, ByVal wFlags As Long)
Private Declare Function DrawMenuBar& Lib "user32" (ByVal hWnd As
Long)
Private hWnd&

Private Sub Workbook_Activate()
DeleteMenu GetSystemMenu(hWnd, 0), &HF060, 0&
DrawMenuBar hWnd
End Sub

Private Sub Workbook_Deactivate()
GetSystemMenu hWnd, True
DrawMenuBar Application.hWnd
End Sub

Private Sub Workbook_Open()
hWnd = FindWindow(vbNullString, Application.Caption)
End Sub

MP

"Nigel" a écrit dans le message de
...
Hi All
Is there a way of disabling the Windows X - application close
button -
after
opening Excel. I know I can do this with a userform but not the
application
as a whole.

Thanks

Nigel

Hi Bob
I thought it might be possbile to include in the Outlook Application_Startup
event, stored as part of ThisOutlookSession project code?
I did try it but it did not work in its current form. I am not sure if this
code runs when Outlook opens or if it does it needs to be ordered
differently.

Cheers
Nigel

"Bob Phillips" wrote in message
...
Nigel,

It currently is triggered when a particular workbook is activated. What
would be the Outlook trigger, that is what so you want to tie it to, and
when would it be invoked?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Nigel" wrote in message
...
Thanks Bob.

Any ideas about converting it for use in Outlook?

Cheers
Nigel

"Bob Phillips" wrote in message
...
It should work okay in XL97 as long as you note the comment that I
made.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Nigel" wrote in message
...
Thanks Michel, this works in my Excel 2002 environment, do you know
if
it
will in Excel97 ?
I do not understand all the intricacies of the subsequent hWnd
debate.

Also will this work in Outlook (2002 and 98) ?

Cheers
Nigel

"Michel Pierron" wrote in message
...
Hi Nigel;
Try this (in ThisWorkbook module):

Private Declare Function FindWindow& Lib "user32" Alias
"FindWindowA"
_
(ByVal lpClassName As String, ByVal lpWindowName As String)
Private Declare Function GetSystemMenu& Lib "user32" _
(ByVal hWnd As Long, ByVal bRevert As Long)
Private Declare Function DeleteMenu& Lib "user32" (ByVal hMenu As
Long
_
, ByVal nPosition As Long, ByVal wFlags As Long)
Private Declare Function DrawMenuBar& Lib "user32" (ByVal hWnd As
Long)
Private hWnd&

Private Sub Workbook_Activate()
DeleteMenu GetSystemMenu(hWnd, 0), &HF060, 0&
DrawMenuBar hWnd
End Sub

Private Sub Workbook_Deactivate()
GetSystemMenu hWnd, True
DrawMenuBar Application.hWnd
End Sub

Private Sub Workbook_Open()
hWnd = FindWindow(vbNullString, Application.Caption)
End Sub

MP

"Nigel" a écrit dans le message de
...
Hi All
Is there a way of disabling the Windows X - application close
button -
after
opening Excel. I know I can do this with a userform but not the
application
as a whole.

Thanks

Nigel

Nigel,

The code as it stands works on a workbook activate. So switching between
workbooks turns it on and off. Do you want it for everything in Outlook?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Nigel" wrote in message
...
Hi Bob
I thought it might be possbile to include in the Outlook
Application_Startup
event, stored as part of ThisOutlookSession project code?
I did try it but it did not work in its current form. I am not sure if
this
code runs when Outlook opens or if it does it needs to be ordered
differently.

Cheers
Nigel

"Bob Phillips" wrote in message
...
Nigel,

It currently is triggered when a particular workbook is activated. What
would be the Outlook trigger, that is what so you want to tie it to, and
when would it be invoked?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Nigel" wrote in message
...
Thanks Bob.

Any ideas about converting it for use in Outlook?

Cheers
Nigel

"Bob Phillips" wrote in message
...
It should work okay in XL97 as long as you note the comment that I
made.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Nigel" wrote in message
...
Thanks Michel, this works in my Excel 2002 environment, do you
know
if
it
will in Excel97 ?
I do not understand all the intricacies of the subsequent hWnd
debate.

Also will this work in Outlook (2002 and 98) ?

Cheers
Nigel

"Michel Pierron" wrote in message
...
Hi Nigel;
Try this (in ThisWorkbook module):

Private Declare Function FindWindow& Lib "user32" Alias
"FindWindowA"
_
(ByVal lpClassName As String, ByVal lpWindowName As String)
Private Declare Function GetSystemMenu& Lib "user32" _
(ByVal hWnd As Long, ByVal bRevert As Long)
Private Declare Function DeleteMenu& Lib "user32" (ByVal hMenu
As
Long
_
, ByVal nPosition As Long, ByVal wFlags As Long)
Private Declare Function DrawMenuBar& Lib "user32" (ByVal hWnd
As
Long)
Private hWnd&

Private Sub Workbook_Activate()
DeleteMenu GetSystemMenu(hWnd, 0), &HF060, 0&
DrawMenuBar hWnd
End Sub

Private Sub Workbook_Deactivate()
GetSystemMenu hWnd, True
DrawMenuBar Application.hWnd
End Sub

Private Sub Workbook_Open()
hWnd = FindWindow(vbNullString, Application.Caption)
End Sub

MP

"Nigel" a écrit dans le message
de
...
Hi All
Is there a way of disabling the Windows X - application close
button -
after
opening Excel. I know I can do this with a userform but not
the
application
as a whole.

Thanks

Nigel

Bob,

Yes I want it disabled whilst the Outlook session is open. The only way to
close would then be via the menu

Cheers
Nigel

"Bob Phillips" wrote in message
...
Nigel,

The code as it stands works on a workbook activate. So switching between
workbooks turns it on and off. Do you want it for everything in Outlook?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Nigel" wrote in message
...
Hi Bob
I thought it might be possbile to include in the Outlook
Application_Startup
event, stored as part of ThisOutlookSession project code?
I did try it but it did not work in its current form. I am not sure if
this
code runs when Outlook opens or if it does it needs to be ordered
differently.

Cheers
Nigel

"Bob Phillips" wrote in message
...
Nigel,

It currently is triggered when a particular workbook is activated.
What
would be the Outlook trigger, that is what so you want to tie it to,
and
when would it be invoked?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Nigel" wrote in message
...
Thanks Bob.

Any ideas about converting it for use in Outlook?

Cheers
Nigel

"Bob Phillips" wrote in message
...
It should work okay in XL97 as long as you note the comment that I
made.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Nigel" wrote in message
...
Thanks Michel, this works in my Excel 2002 environment, do you
know
if
it
will in Excel97 ?
I do not understand all the intricacies of the subsequent hWnd
debate.

Also will this work in Outlook (2002 and 98) ?

Cheers
Nigel

"Michel Pierron" wrote in message
...
Hi Nigel;
Try this (in ThisWorkbook module):

Private Declare Function FindWindow& Lib "user32" Alias
"FindWindowA"
_
(ByVal lpClassName As String, ByVal lpWindowName As String)
Private Declare Function GetSystemMenu& Lib "user32" _
(ByVal hWnd As Long, ByVal bRevert As Long)
Private Declare Function DeleteMenu& Lib "user32" (ByVal hMenu
As
Long
_
, ByVal nPosition As Long, ByVal wFlags As Long)
Private Declare Function DrawMenuBar& Lib "user32" (ByVal hWnd
As
Long)
Private hWnd&

Private Sub Workbook_Activate()
DeleteMenu GetSystemMenu(hWnd, 0), &HF060, 0&
DrawMenuBar hWnd
End Sub

Private Sub Workbook_Deactivate()
GetSystemMenu hWnd, True
DrawMenuBar Application.hWnd
End Sub

Private Sub Workbook_Open()
hWnd = FindWindow(vbNullString, Application.Caption)
End Sub

MP

"Nigel" a écrit dans le message
de
...
Hi All
Is there a way of disabling the Windows X - application
close
button -
after
opening Excel. I know I can do this with a userform but not
the
application
as a whole.

Thanks

Nigel