I made an Excelform.
If a user closes the form with the X-button (Cancel) at the top righthand
corner, other macro's will give problems, so I dont want the form to be
closed with this button.

In Access you can hide this button, but so far I can see that is not
possible in Excel. How can I prevent that the user uses this Cancelbutton?

Anne

Anne,

One way is to intercept the X-button in the QueryClose event of the
userform. Try something like this in the QueryClose event to cancel the
event if the user attempts to close it with the X-button:

'Check if the user is trying to close the form with the X-button
If CloseMode = vbFormControlMenu Then
Cancel = True
Exit Sub
End If



--
Hope that helps.

Vergel Adriano


"Anne Schouten" wrote:

I made an Excelform.
If a user closes the form with the X-button (Cancel) at the top righthand
corner, other macro's will give problems, so I dont want the form to be
closed with this button.

In Access you can hide this button, but so far I can see that is not
possible in Excel. How can I prevent that the user uses this Cancelbutton?

Anne

I made an Excelform.
If a user closes the form with the X-button (Cancel) at the top righthand
corner, other macro's will give problems, so I dont want the form to be
closed with this button.

In Access you can hide this button, but so far I can see that is not
possible in Excel. How can I prevent that the user uses this Cancelbutton?

I can't see a way to hide it either. You could try this work-around I guess.
Add the following to the UserForm's code window...

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Form cannot be closed at this time.", vbInformation
End If
End Sub

Any attempt to use the "X-Button" will be met with a MessageBox informing
the user the form cannot be closed at this time (you can, of course, change
the message to suit your needs).

Rick

Maybe you could just call your Cancelbutton's _click event:

Option Explicit
Private Sub CommandButton2_Click()
MsgBox "closing"
Unload Me
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Call CommandButton2_Click
End If
End Sub



Anne Schouten wrote:

I made an Excelform.
If a user closes the form with the X-button (Cancel) at the top righthand
corner, other macro's will give problems, so I dont want the form to be
closed with this button.

In Access you can hide this button, but so far I can see that is not
possible in Excel. How can I prevent that the user uses this Cancelbutton?

Anne

--

Dave Peterson

I would go with the QueryClose approach as others have suggested.

FWIW you could grey out and disable the X-button -

Option Explicit

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

Private Const SC_CLOSE = &HF060&

Private Sub UserForm_Initialize()
Dim hWnd As Long, hMenu As Long
hWnd = FindWindow("ThunderDFrame", Me.Caption)
hMenu = GetSystemMenu(hWnd, 0)
RemoveMenu hMenu, SC_CLOSE, 0
End Sub

' make sure you have a way to close the form
Private Sub CommandButton1_Click()
Unload Me
End Sub

Regards,
Peter T

"Anne Schouten" wrote in message
...
I made an Excelform.
If a user closes the form with the X-button (Cancel) at the top righthand
corner, other macro's will give problems, so I dont want the form to be
closed with this button.

In Access you can hide this button, but so far I can see that is not
possible in Excel. How can I prevent that the user uses this Cancelbutton?

Anne

I would go with the QueryClose approach as others have suggested.

FWIW you could grey out and disable the X-button -

Option Explicit

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

Private Const SC_CLOSE = &HF060&

Private Sub UserForm_Initialize()
Dim hWnd As Long, hMenu As Long
hWnd = FindWindow("ThunderDFrame", Me.Caption)
hMenu = GetSystemMenu(hWnd, 0)
RemoveMenu hMenu, SC_CLOSE, 0
End Sub

' make sure you have a way to close the form
Private Sub CommandButton1_Click()
Unload Me
End Sub

Regards,
Peter T

"Anne Schouten" wrote in message
...
I made an Excelform.
If a user closes the form with the X-button (Cancel) at the top righthand
corner, other macro's will give problems, so I dont want the form to be
closed with this button.

In Access you can hide this button, but so far I can see that is not
possible in Excel. How can I prevent that the user uses this Cancelbutton?

Anne

Thank you all.

The query_Close method works perfect.

The option to disable the X-button works aswel, but I don't understand the
code. Can you tell me where to find explanation for it.

Anne


"Anne Schouten" wrote in message
...
I made an Excelform.
If a user closes the form with the X-button (Cancel) at the top righthand
corner, other macro's will give problems, so I dont want the form to be
closed with this button.

In Access you can hide this button, but so far I can see that is not
possible in Excel. How can I prevent that the user uses this Cancelbutton?

Anne

The call to the FindWindow API finds the window handle of your userform,
with the search filtered to the 'classname' of Userforms (ThunderDFrame in
VBA offiice 2000+), and the form's caption.

Calls to API's GetSystemMenu and RemoveMenu are detailed in the following
links -

http://msdn2.microsoft.com/en-us/library/ms647985.aspx

http://msdn2.microsoft.com/en-us/library/ms647994.aspx

The constant SC_CLOSE is so named by convention, but could be named
anything, or simply the value &HF060& or 61536& could be passed to identify
in particular it's the close button to be removed (or greyed).

Regards,
Peter T


"Anne Schouten" wrote in message
...
Thank you all.

The query_Close method works perfect.

The option to disable the X-button works aswel, but I don't understand the
code. Can you tell me where to find explanation for it.

Anne


"Anne Schouten" wrote in message
...
I made an Excelform.
If a user closes the form with the X-button (Cancel) at the top
righthand
corner, other macro's will give problems, so I dont want the form to be
closed with this button.

In Access you can hide this button, but so far I can see that is not
possible in Excel. How can I prevent that the user uses this
Cancelbutton?

Anne

Hoi Peter,

Thank you very much for your answer ans the links.

Regards,
Anne

"Peter T" <peter_t@discussions wrote in message
...
The call to the FindWindow API finds the window handle of your userform,
with the search filtered to the 'classname' of Userforms (ThunderDFrame in
VBA offiice 2000+), and the form's caption.

Calls to API's GetSystemMenu and RemoveMenu are detailed in the following
links -

http://msdn2.microsoft.com/en-us/library/ms647985.aspx

http://msdn2.microsoft.com/en-us/library/ms647994.aspx

The constant SC_CLOSE is so named by convention, but could be named
anything, or simply the value &HF060& or 61536& could be passed to
identify
in particular it's the close button to be removed (or greyed).

Regards,
Peter T


"Anne Schouten" wrote in message
...
Thank you all.

The query_Close method works perfect.

The option to disable the X-button works aswel, but I don't understand
the
code. Can you tell me where to find explanation for it.

Anne


"Anne Schouten" wrote in message
...
I made an Excelform.
If a user closes the form with the X-button (Cancel) at the top
righthand
corner, other macro's will give problems, so I dont want the form to
be
closed with this button.

In Access you can hide this button, but so far I can see that is not
possible in Excel. How can I prevent that the user uses this
Cancelbutton?

Anne