I'm trying to find the coding to disable the red 'X' in the right hand corner
of any Excel worksheet. I know what the code is for a form, but can't find
the code for the worksheet.

The code I'm using for my form is the following:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button !!"
End If
End Sub


Any help would be appreciated.

Steve

Hi,

you can disabled "X" controlbox by API calls like:
Private Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function FindWindowEx Lib "user32" Alias _
"FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

Private Declare Function GetSystemMenu Lib "user32" _
(ByVal hwnd As Long, ByVal bRevert As Long) As Long

Private Declare Function DeleteMenu Lib "user32" _
(ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As
Long

Private Const SC_CLOSE As Long = &HF060 'Close "x" box

Sub DisabledX()
Dim Hwndx As Long, xBox As Long

If Val(Application.Version) <= 9 Then
Hwndx = FindWindow("XLMAIN", Application.Caption) 'xl2000 and earlier
Else
Hwndx = Application.hwnd 'xl2002 and later
End If

Hwndx = FindWindowEx(Hwndx, 0, "XLDESK", vbNullString)
Hwndx = FindWindowEx(Hwndx, 0, "EXCEL7", ActiveWindow.Caption)

xBox = GetSystemMenu(Hwndx, 0)

DeleteMenu xBox, SC_CLOSE, 0&
'Doesnt work unless you change the window state of worksheet/book to
restore

End Sub


--

Regards,

Halim


"beginner here" wrote:

> I'm trying to find the coding to disable the red 'X' in the right hand corner
> of any Excel worksheet. I know what the code is for a form, but can't find
> the code for the worksheet.
>
> The code I'm using for my form is the following:
>
> Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
> If CloseMode = vbFormControlMenu Then
> Cancel = True
> MsgBox "Please use the button !!"
> End If
> End Sub
>
>
> Any help would be appreciated.
>
> Steve
>

Just a curiosity question...

Why do you want to stop the user from using the X button? If there's some code
in the Cancel button that needs to be run, couldn't your userform_queryclose
procedure call that cancel button's procedure?



beginner here wrote:
>
> I'm trying to find the coding to disable the red 'X' in the right hand corner
> of any Excel worksheet. I know what the code is for a form, but can't find
> the code for the worksheet.
>
> The code I'm using for my form is the following:
>
> Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
> If CloseMode = vbFormControlMenu Then
> Cancel = True
> MsgBox "Please use the button !!"
> End If
> End Sub
>
> Any help would be appreciated.
>
> Steve

--

Dave Peterson

Becasue I have reduced the size of the worksheet to match my userform size.
And by clicking on the red X to close this workbook the next time they open
Excel, the book1 sheet will come up in the same size. So I want them to have
click on the close button to return to the max size.

Steve Martin

"Dave Peterson" wrote:

> Just a curiosity question...
>
> Why do you want to stop the user from using the X button? If there's some code
> in the Cancel button that needs to be run, couldn't your userform_queryclose
> procedure call that cancel button's procedure?
>
>
>
> beginner here wrote:
> >
> > I'm trying to find the coding to disable the red 'X' in the right hand corner
> > of any Excel worksheet. I know what the code is for a form, but can't find
> > the code for the worksheet.
> >
> > The code I'm using for my form is the following:
> >
> > Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
> > If CloseMode = vbFormControlMenu Then
> > Cancel = True
> > MsgBox "Please use the button !!"
> > End If
> > End Sub
> >
> > Any help would be appreciated.
> >
> > Steve
>
> --
>
> Dave Peterson
>