Trap Close event at worksheet level?

[Rich Wallace]

Hi all,

I have three issues I'm hoping somone can help with:

1. Is there a way I can trap a 'Close' event at the worksheet level rather
than the Workbook level? I've tried Worksheet_Deactivate but it's not
working quite as well as I had hoped.

2. If/When a user closes the worksheet and opts to save changes when
prompted, can I trap the data from the cell that was last updated aside from
using Worksheet_SelectionChange (currently I use Worksheet_Change).

3. Upon a user being prompted to save data, is there a way, again at the
worksheet elvel, to know what the user selected? If they opt to save data,
I want to run one macro, if they do NOT save, I want to run a different one.

TIA
-Rich

 

[Vasant Nanavati]

How do you close a worksheet?

 

[Rich Wallace]

That's what I'm trying to find out I guess and how to trap it. An Excel
Application opens a worksheet stored on our network; the application allows
user to update certain fields and can then close that individual 'file' they
updated but still leave the main application running. I need to know when
and how to catch the user closing the file as the Workbook_Close is not
firing.

 

[Frank Kabel]

Hi Rich
i think you should be precise with your wording:
1. WorkSHEET: a single table within a file/workBOOK
- can't be closed individually (can be hidden)
- no closing event on worksheet level

2. WorkBOOK: One Excel file consisiting of 1 or more worksheets
('filename.xls')
- Private Sub Workbook_BeforeClose(Cancel As Boolean) should be
'frired' if you close this file/workbook
- Excel as application can house one or more workBOOKs
- there's no workbook_close event

 

[Rich Wallace]

Thank you for the clarification, still trying to learn the Excel world, I'll
try teh different approach agani, thank you!

 

[Rich Wallace]

Can anybody still help with how I can trap the save return from the GUI to
know if a user has chosen to save changes or not.

 

[Vasant Nanavati]

You would have to suppress the "Would you like to save ..." built-in prompt
and substitute your own. See John Walkenbach's solution:

http://j-walk.com/ss/excel/tips/tip78.htm

 

[Rich Wallace]

Thank you, it's exactly what I needed!

You would have to suppress the "Would you like to save ..." built-in prompt
and substitute your own. See John Walkenbach's solution:

http://j-walk.com/ss/excel/tips/tip78.htm

 

[Vasant Nanavati]

You're welcome; thanks for the feedback!