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

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

--
Regards,
Tom Ogilvy

"Stefi" wrote in message
...
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" wrote in message
...
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