conflict between auto save&close macro and show/hide sheets macro

**alexandruc** · 10-16-2009, 07:26 AM

Hello

I am trying to make a save&close workbook macro.

I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).

The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.

If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.

The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)

The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.

Can anyone help me adjust the code in the attached file so that it saves before auto closing?

Thank you

p.s: I raised the same problem with no success so far here:
http://www.thecodecage.com/forumz/me...ets-macro.html
here:
conflict between auto save&close macro and show/hide sheets macro - dBforums
and here:
conflict between auto save&close macro and show/hide sheets macro - MrExcel Message Board

**Rick_Stanich** · 10-16-2009, 10:12 AM

In the Case statement where you dont want to save add this line:

ActiveWorkBook.Close, False

It seemed to work for me.

HTH

**alexandruc** · 10-16-2009, 10:35 AM

no, it does not autosave before auto closing.

If you open the file (enable macros), type something... and then wait 15 seconds, the workbook will close but it does NOT save.

I need to know how to modify the code so that it will save before closing...

**Rick_Stanich** · 10-16-2009, 10:37 AM

When I learn to read, I will notice things like this;

so that it will save before closing

**alexandruc** · 10-16-2009, 12:43 PM

so... anyone has a solution to help fix this problem?

**Timothy J. Lanza** · 10-16-2009, 04:00 PM

If it always needs to save when it closes, you can use the BeforeClose event, like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    ActiveWorkbook.Save
    
End Sub

It will always save before the close action. It should work whether its triggered from another procedure or by closing the window manually.

**alexandruc** · 10-18-2009, 10:02 AM

The macros I have already use a Workbook_BeforeClose.

Anyhow it's not a solution since I don't want to force a save on a manual close of excel (maybe a user made a mistake that he does not want to save...).

I want it to auto save only as part of the auto close macro.

Any other ideas how I can make this work?

**royUK** · 10-18-2009, 10:46 AM

See if this is any better

**alexandruc** · 10-18-2009, 10:52 AM

Originally Posted by royUK

See if this is any better

sorry... I don't know what I should look at on that link...

**royUK** · 10-18-2009, 11:33 AM

Sorry, I pasted the wrong link. It should be OK now

**alexandruc** · 10-18-2009, 11:56 AM

The ForceUsersEnableMacros on your link is not working right for me.

If I modify something on sheet2 or 3 and save, the first sheet becomes visible and sheet2 and 3 become hidden. So the save does not function your example of hide/show sheets.

The macro in my example has this problem fixed using a custom save... which affects the save&close macro... and I hope someone can make them work together since I don't really know VB or excel programming...