How does one remove the upper right hand close button on the form? I have a
form that opens on work book open and i want to remove that option from the
user.

Don't remove it, just trap the click.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
MsgBox "Close mode is " & CloseMode
End Sub


from help


Occurs before a UserForm closes.


Syntax


Private Sub UserForm_QueryClose(cancel As Integer, closemode As Integer)


The QueryClose event syntax has these parts:


Part Description
cancel An integer. Setting this argument to any value other than 0
stops the QueryClose event in all loaded user forms and prevents the
UserForm and application from closing.
closemode A value or constant indicating the cause of the QueryClose
event.


Return Values


The closemode argument returns the following values:


Constant Value Description
vbFormControlMenu 0 The user has chosen the Close command from the
Control menu on the UserForm.
vbFormCode 1 The Unload statement is invoked from code.
vbAppWindows 2 The current Windows operating environment session is
ending.
vbAppTaskManager 3 The Windows Task Manager is closing the
application.


These constants are listed in the Visual Basic for Applications object
library in the Object Browser. Note that vbFormMDIForm is also specified in
the Object Browser, but is not yet supported.


Remarks


This event is typically used to make sure there are no unfinished tasks in
the user forms included in an application before that application closes.
For example, if a user hasn't saved new data in any UserForm, the
application can prompt the user to save the data.


When an application closes, you can use the QueryClose event procedure to
set the Cancel property to True, stopping the closing process.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
How does one remove the upper right hand close button on the form? I have
a
form that opens on work book open and i want to remove that option from
the
user.

Hei JK!

Was it that code you like to find?
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
End If
End Sub

Regards Kari J Keinonen



"jk" wrote:

How does one remove the upper right hand close button on the form? I have a
form that opens on work book open and i want to remove that option from the
user.

To disable it you can use the Windows API:

In a normal module have this:

Option Explicit
Public Declare Function GetActiveWindow Lib "user32" () As Long
Private Declare Function DrawMenuBar Lib "user32" _
(ByVal hwnd As Long) As Long
Private Declare Function GetMenuItemCount Lib "user32" _
(ByVal hMenu As Long) As Long
Private Declare Function GetSystemMenu Lib "user32" _
(ByVal hwnd As Long, _
ByVal bRevert As Long) As Long
Private Declare Function RemoveMenu Lib "user32" _
(ByVal hMenu As Long, _
ByVal nPosition As Long, _
ByVal wFlags As Long) _
As Long
Private Const MF_BYPOSITION = &H400
Private Const MF_REMOVE = &H1000

Sub DisableCloseButton(hwnd As Long)

Dim hMenu As Long
Dim menuItemCount As Long

hMenu = GetSystemMenu(hwnd, 0)

If hMenu Then
menuItemCount = GetMenuItemCount(hMenu)
RemoveMenu hMenu, _
menuItemCount - 1, _
MF_REMOVE Or MF_BYPOSITION
DrawMenuBar hwnd
End If

End Sub


In the UserForm code have this:

Private Sub UserForm_Activate()
DisableCloseButton GetActiveWindow()
End Sub

You will need some sort of control on the form to unload it:

Private Sub CommandButton1_Click()
Unload Me
End Sub


RBS

"jk" wrote in message
...
How does one remove the upper right hand close button on the form? I have
a
form that opens on work book open and i want to remove that option from
the
user.

RB Smissaert, good, thank you,
by the way, with the method, how can i remove maximize button of not
form but excel window?

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

Not sure it is a good thing to do, but you will need instead the Window
handle of Excel.
In later versions (I think from 2002) you can get that with
Application.Hwnd.

RBS

"x taol" wrote in message
...


RB Smissaert, good, thank you,
by the way, with the method, how can i remove maximize button of not
form but excel window?

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