How to disable a sheet's x(close) button?

Hi all,
How can I disable a sheet's Close, Small size, Full size/Previous size
buttons (in the top right corner)? I want to manage the sheet from a Userform!
Thanks! Stefi

click tools>protection>protect workbook tick both options should take it away as well as you can add a control to stop users closing the user form if you want.

'Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
' If CloseMode = vbFormControlMenu Then
' Cancel = True
' End If
'End Sub

The above stops people closing the tab

Protect the workbook (tools=>protection=>Workbook). Protect at least the
Structure option.

--
Regards,
Tom Ogilvy

"Stefi" <Stefi@discussions.microsoft.com> wrote in message
news:B72ED95C-E0BC-4745-9408-21A6F8DE9BC4@microsoft.com...
> Hi all,
> How can I disable a sheet's Close, Small size, Full size/Previous size
> buttons (in the top right corner)? I want to manage the sheet from a
Userform!
> Thanks! Stefi

Trapping the Close is the easiest: there is an event procedure for
BeforeClose that has a parameter called Cancel; setting Cancel to true in
your procedure will cancel the event, so enter this in the ThisWorkbook
module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' You can put a MsgBox or something here to alert the user if desired
Cancel=True
End Sub

There is also a WindowResize event for the workbook, and you could do this:

Private Sub Workbook_WindowResize(ByVal Wn As Window)
Wn.WindowState = xlMaximized
' Or, your could set any other properties of the window by using Wn,
which is the
' object that holds your Workbook's window
End Sub

The above will prevent the Workbook window (inside the Excel window) from
being set to anything but Maximized, but the Excel app itself could be
resized or minimized. To keep the Excel Window from being changed is much
trickier: there is an Excel Application WindowResize event, too, but to trap
it you would have to declare a custom class an object variable for an
Excel.Application declared "WithEvents" so you can write an event procedure;
if you are not familiar with class modules and event trapping this is an
extensive subject, all I can do in limited time and space is refer you to
other places for info - search this newsgroup or the MSDN library and you
will find more info on this.

HTH - K Dales

"Stefi" wrote:

> Hi all,
> How can I disable a sheet's Close, Small size, Full size/Previous size
> buttons (in the top right corner)? I want to manage the sheet from a Userform!
> Thanks! Stefi

That was a really detailed and understandable explanation!
Thank you very much!
Stefi
P.S. You mean "easiest" for experts and gurus, don't you?

„K Dales” ezt �*rta:

> Trapping the Close is the easiest: there is an event procedure for
> BeforeClose that has a parameter called Cancel; setting Cancel to true in
> your procedure will cancel the event, so enter this in the ThisWorkbook
> module:
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> ' You can put a MsgBox or something here to alert the user if desired
> Cancel=True
> End Sub
>
> There is also a WindowResize event for the workbook, and you could do this:
>
> Private Sub Workbook_WindowResize(ByVal Wn As Window)
> Wn.WindowState = xlMaximized
> ' Or, your could set any other properties of the window by using Wn,
> which is the
> ' object that holds your Workbook's window
> End Sub
>
> The above will prevent the Workbook window (inside the Excel window) from
> being set to anything but Maximized, but the Excel app itself could be
> resized or minimized. To keep the Excel Window from being changed is much
> trickier: there is an Excel Application WindowResize event, too, but to trap
> it you would have to declare a custom class an object variable for an
> Excel.Application declared "WithEvents" so you can write an event procedure;
> if you are not familiar with class modules and event trapping this is an
> extensive subject, all I can do in limited time and space is refer you to
> other places for info - search this newsgroup or the MSDN library and you
> will find more info on this.
>
> HTH - K Dales
>
> "Stefi" wrote:
>
> > Hi all,
> > How can I disable a sheet's Close, Small size, Full size/Previous size
> > buttons (in the top right corner)? I want to manage the sheet from a Userform!
> > Thanks! Stefi

Checking the Window option was also necessary, it did exactly what I wanted.
Thanks Tom!
Stefi

„Tom Ogilvy” ezt �*rta:

> Protect the workbook (tools=>protection=>Workbook). Protect at least the
> Structure option.
>
> --
> Regards,
> Tom Ogilvy
>
> "Stefi" <Stefi@discussions.microsoft.com> wrote in message
> news:B72ED95C-E0BC-4745-9408-21A6F8DE9BC4@microsoft.com...
> > Hi all,
> > How can I disable a sheet's Close, Small size, Full size/Previous size
> > buttons (in the top right corner)? I want to manage the sheet from a
> Userform!
> > Thanks! Stefi
>
>
>