auto_close give me additional information?

S

SteAXA

Hi to everybody,
exists a way to know if i close only a workbook or the entire excel
application, when the users close the application by "X"? Exists anything
connected with auto_close that can take me that information?
I need this because when the user close a workbook or a application i need
to clean up an additional menu (that i've previously added for each workbook
opened by a macro) for the single workbook.
Thanks, Ste'
 
J

Jacob Skaria

Use the Workbook_BeforeClose event..

From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the
left treeview search for the workbook name and click on + to expand it.
Within that you should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "About to close"
End Sub
 
J

JLatham

A couple of hints that may prove helpful to you with this also, since you're
working with custom menus.

When the workbook Opens, not only does its _Open() event fire, but so does
its _Activate() event. Likewise, when it is closed, both the _BeforeClose()
and _Deactivate() events fire off.

When working with custom menus in a workbook, I usually put my "create
custom menus" code into the Workbook_Activate() event code, and the "destroy
custom menus" code into the Workbook_Deactivate() event. This way, if a
person has multiple workbooks open, as they switch between them, they don't
have the (inappropriate) custom menu available in another workbook. But as
soon as they choose the book with the needed custom menu again, it will
reappear in that book -- which it wouldn't do if only controlled by _Open()
and _BeforeClose().
 
S

SteAXA

Thanks very much for your hints, now my application works more better!
I've used the workbook event for create and delete custum menus, and it's
more easy to manage.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top