How do I prevent or disable in code a worksheet close, not a workbook close?

Thanks

You cannot cancel the deactivate, so you would need to capture the active
worksheet, and then restore that if it is deactivated.

--

HTH

RP

"Sheldon" wrote in message
...
How do I prevent or disable in code a worksheet close, not a workbook
close?

Thanks

Sheldon,
Do you mean deactivate a worksheet? Try:
Private Sub Worksheet_Deactivate()
Me.Activate
End Sub

Geof.
-----Original Message-----
How do I prevent or disable in code a worksheet close,
not a workbook close?

Thanks
.

Hi Sheldon,

A worksheet can not be closed, merely deactivated or hidden.

Try setting the visible property of all but the required sheet to
xlVeryHidden:

Sub Tester()
Dim sh As Object
For Each sh In ActiveWorkbook.Sheets
If sh.Name < "Permitted Sheet's name" Then
sh.Visible = xlVeryHidden
End If
Next
End Sub

If this approach is not acceptable you could paste something like the
following into the worksheet module:

Private Sub Worksheet_Deactivate()
MsgBox "Only sheet " & Me.Name & " is available", _
vbInformation
Me.Activate
End Sub

---
Regards,
Norman



"Sheldon" wrote in message
...
How do I prevent or disable in code a worksheet close, not a workbook
close?

Thanks

I guess I need to be more specific. I want to prevent the automatic action
that takes place when the user clicks on the "X" in the top right of the
worksheet (NOT the workbook - that's ok to use). I have created a macro that
closes the worksheet and does some other operations.

Thanks

"Sheldon" wrote:

How do I prevent or disable in code a worksheet close, not a workbook close?

Thanks

Hi Sheldon.

The "X" you refer to closes the workbook, not a sheet. The topmost "X"
closes the Excel Application.

I have created a macro that closes the worksheet

Please show your code! I do not beleive that it is possible to close a
worksheet in isolation from its parent workbook.


---
Regards,
Norman



"Sheldon" wrote in message
...
I guess I need to be more specific. I want to prevent the automatic action
that takes place when the user clicks on the "X" in the top right of the
worksheet (NOT the workbook - that's ok to use). I have created a macro
that
closes the worksheet and does some other operations.

Thanks

"Sheldon" wrote:

How do I prevent or disable in code a worksheet close, not a workbook
close?

Thanks

I don't know how I can be more explicit. I'm not referring to the TOP,
rightmost "X", which closes the applications. I have several worksheets in
my workbook app. When I need to display two of the worksheets at the same
time, each taking part of the app window, I need to prevent the user from
clicking the worksheet "x" when that sheet is active.
I cannot seem to trap that event.

Thanks
Sheldon

"Norman Jones" wrote:

Hi Sheldon.

The "X" you refer to closes the workbook, not a sheet. The topmost "X"
closes the Excel Application.

I have created a macro that closes the worksheet

Please show your code! I do not beleive that it is possible to close a
worksheet in isolation from its parent workbook.


---
Regards,
Norman



"Sheldon" wrote in message
...
I guess I need to be more specific. I want to prevent the automatic action
that takes place when the user clicks on the "X" in the top right of the
worksheet (NOT the workbook - that's ok to use). I have created a macro
that
closes the worksheet and does some other operations.

Thanks

"Sheldon" wrote:

How do I prevent or disable in code a worksheet close, not a workbook
close?

Thanks

Protect the Workbook, windows option. This also prevents moving the
windows.

Tools=Protection=Protect Workbook = Choose at least Windows
--
Regards,
Tom Ogilvy

"Sheldon" wrote in message
...
I don't know how I can be more explicit. I'm not referring to the TOP,
rightmost "X", which closes the applications. I have several worksheets
in
my workbook app. When I need to display two of the worksheets at the same
time, each taking part of the app window, I need to prevent the user from
clicking the worksheet "x" when that sheet is active.
I cannot seem to trap that event.

Thanks
Sheldon

"Norman Jones" wrote:

Hi Sheldon.

The "X" you refer to closes the workbook, not a sheet. The topmost "X"
closes the Excel Application.

I have created a macro that closes the worksheet

Please show your code! I do not beleive that it is possible to close a
worksheet in isolation from its parent workbook.


---
Regards,
Norman



"Sheldon" wrote in message
...
I guess I need to be more specific. I want to prevent the automatic
action
that takes place when the user clicks on the "X" in the top right of
the
worksheet (NOT the workbook - that's ok to use). I have created a
macro
that
closes the worksheet and does some other operations.

Thanks

"Sheldon" wrote:

How do I prevent or disable in code a worksheet close, not a workbook
close?

Thanks