Workbook_BeforeClose

[Ed Davis]

Posted in another news group but no response.

I have a situation where I have 7 workbooks open at any given time.
I have created macros that saves, backups all workbooks and then Quits Excel
when exiting using a command button.
I have found that the users are sometimes X'ing out of excel and not saving
the changes.
I therefore added the following to the Thisworkbook module.
What happens now is if the user X's out it will save however, if they use
the command button it saves the workbooks two times. But it never closes the
workbooks or Quits Excel.
Is there another way to get around this dilemma?


Private Sub Workbook_BeforeClose(Cancel As Boolean)

If ThisWorkbook.Saved = True Then
MsgBox "Saved Will Close"
Else
MsgBox "Not Saved Will Save"
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
End If
End Sub

 

[JLatham]

Have you tried declaring a flag that would be set to TRUE when the user
clicks the button to close and save? Then you can test that flag in the
_BeforeClose routine to see if it needs to take action or not. Your
_BeforeClose routine could then look something like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not closingWithButton Then
If ThisWorkbook.Saved = True Then
MsgBox "Saved Will Close"
Else
MsgBox "Not Saved Will Save"
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
End If
End IF
End Sub

 

[Ed Davis]

I have tried what you gave me below and I must be doing something wrong.
This is what I have:
In the macro that is called from the button I Call the Save_All
Notice I put the correct statement in Save_Me as it does not become true
until the save.

Call Save_All

The save all is as follows:

Sub Save_All()
Dim WBook As Workbook
On Error Resume Next
For Each WBook In Application.Workbooks
Application.DisplayAlerts = False
Application.Run (WBook.Name & "!Save_Me")
Application.DisplayAlerts = True
Next WBook
End Sub
And that macro calls the Save_Me macro that looks like this:

Sub Save_Me()
Dim strSH As String
Dim correct As Boolean
strSH = ActiveSheet.Name
Application.DisplayAlerts = False
ThisWorkbook.Save
correct = True
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\Backup\Loja\Loja
Normal Close (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls"
Application.DisplayAlerts = True
End Sub

And after all saves are made from the first macro I quit.
Application.Quit

 

[JLatham]

I assume that the flag is the 'correct' Boolean?

If that's the case, you need to declare it as
Public correct As Boolean

in some regular module before any Sub or Function declaration. Because it's
declared inside of the Save_Me() macro, it does not exist outside of that
macro, so the _BeforeClose code never sees it.

 

[Ed Davis]

Did everything you posted but it stops at the msgbox "saved"
and never closes.

 

[JLatham]

Ed,
Thought I'd start a new line of discussion off of your origina post here.

I think the problem may have been caused by my not fully understanding what
was going on here. I was under the impression that all of this was going on
in one workbook, but I realize now that you're actually trying to work across
several different workbooks. That's another part of why the others aren't
paying attention to the Public boolean value - it doesn't exist in the other
workbooks.

I'll have to give this more thought and try some things. But I need to have
at least one question answered:

This button that's used to close all of the workbooks, does it exist in all
of the workbooks, or just one "master control" workbook?

Ok, second question: Do all of the workbooks have the same macros in them?

 

[Ed Davis]

The button runs the macro from the master control workbook.
All workbooks do have the Save_All and Save_Me macros.
Most of the workbooks do have the same macro but these two macros are in all
workbooks.
The macros that I am using do not really close the wrokbooks, they save the
workbooks and then I quit the application
the workbooks are saved. If you would like I can post the three macros again
to give you an idea as to what they are doing?

Thank you very much for your help.

 

[JLatham]

Ed,
No need to repost the code, I can see it in the earlier posts. I just
needed to make sure that I understand the whole architecture of the
operation. I think I can work something up for you pretty easily this
evening now.

 

[Ed Davis]

Again thank you very much. We have lost several days work due to this
problem.

 

[JLatham]

I believe this accomplishes the task.

Put this code in a regular code module in the MASTER workbook - it's a
replacement for both your current Save_All and the Save_Me code.

Option Explicit ' very first line in the code module
Public ClosingWithSaveAll As Boolean

Sub Save_All()
Dim WBook As Workbook ' some other workbook
'set the flag so that ThisWorkbook.BeforeClose knows about this
ClosingWithSaveAll = True
' On Error Resume Next
' prevent triggering the _BeforeClose event in other workbooks
Application.EnableEvents = False
'prevent as much screen flickering as possible
Application.ScreenUpdating = False ' automatically resets when this Sub
ends.
For Each WBook In Application.Workbooks
If WBook.Name <> ThisWorkbook.Name Then
'this all replaces the Save_Me() code
Application.DisplayAlerts = False
WBook.Save 'saves with changes
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & _
"\Backup\Loja\Loja Normal Close (" & _
WBook.ActiveSheet.Name & ")" _
& Format(Now, " dd-mm-yy hh-mm-ss") & ".xls"
WBook.Close False ' already saved with changes, just close it
Application.DisplayAlerts = True
End If
Next WBook
Set WBook = Nothing
'don't forget to re-enable event/interrupt processing
'clear any error that may be left over also
If Err <> 0 Then
Err.Clear
End If
On Error GoTo 0 ' reset error trapping
Application.EnableEvents = True
'and finally, save this workbook, a copy of it, and close Excel
ThisWorkbook.Save
'any code needed to save a copy of this Master book goes here
'
'
Application.Quit
End Sub

And in the MASTER workbook's ThisWorkbook_BeforeClose() event use this code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not ClosingWithSaveAll Then
Cancel = True
Save_All ' call the master shut-down code in this workbook
End If
End Sub

Finally, you can go back to your original code in the non-master workbooks:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Saved = True Then
MsgBox "Saved Will Close"
Else
MsgBox "Not Saved Will Save"
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
End If
End Sub

 

[Ed Davis]

Thank you so much.
I am putting the code in now and noticed that the code line:

ThisWorkbook.SaveCopyAs ThisWorkbook.Path & _
"\Backup\Loja\Loja Normal Close (" & _
WBook.ActiveSheet.Name & ")" _
& Format(Now, " dd-mm-yy hh-mm-ss") & ".xls"

Should be different for each workbook.
What the difference would be is the ("\Loja\Loja") in this case.
The workbook names begin with "NAME " and then "MM-YYYY",
So one would be "\Sales\Sales 09-2009 Normal Close"
Another would be "\Invoice\Invoice 09-2009 Normal Close" and so on.
So, anything before the MM-YYYY would be the workbook.name.

Also if the close is normal I really do not need the time in the filename. I
know how to get rid of that though.

I am going to try what you gave me and will let you know.

 

[Ed Davis]

After making a couple of changes,
Everything works great except for 2 things.

1. The master workbook is saved 7 times as "Loja normal close". Loja
never saved as "Loja Normal close."
2. Whenever someone exits using the X I save the file telling me they
used the X out and when they did it.
The master workbook never saves telling me they used the X out.
I use the following code in the workbook_beforeclose module.
The path would be different.

ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\Backup\Combust\Error\Diesel X
Close (" & _
ThisWorkbook.ActiveSheet.Name & ")" & Format(Now, " dd-mm-yy
hh-mm-ss") & ".xls"

 

[Ed Davis]

I think I fixed the #2. from the previous post.

 

[Ed Davis]

#2. If working properly now.
The only issue is the master being saved 7 times and Loja not being saved.
(as Normal Close)

 

[JLatham]

I'll look at this in more detail this evening. But some quick comments:

As you can see in the code WBook.Name gives you the name of the current
workbook that is being dealt with. You can use that to determine which path
to save either with a Select Case or series of If .. Then statements.

The master book is saving 7 times because of an error I put back into the
code. In the middle of the For Each WBook loop, I referenced ThisWorkbook.
and that probably should have been WBook.SaveCopyAs ...

That line may even need to have been put down in the code right after
ThisWorkbook.Save and before Application.Quit

 

[Ed Davis]

I tried this but I get an error:
The error I get is Compile error
Sub or function not defined. FIND is highlighted.


ThisWorkbook.SaveCopyAs ThisWorkbook.Path & _
"\Backup\" & Left(ThisWorkbook.Name, Find(" ", ThisWorkbook.Name)) &
"\" & Left(ThisWorkbook, Name, Find(" ", ThisWorkbook.Name)) & _
WBook.ActiveSheet.Name & ")" & Format(Now, " dd-mm-yy hh-mm-ss") &
".xls"

 

[Ed Davis]

I now changed a couple of things and still get the compile error
..
WBook.SaveCopyAs WBook.Path & "\Backup\" & Left(WBook, Find(" ", WBook)) &
"\" & Left(WBook, Find(" ", WBook)) & _
WBook.ActiveSheet.Name & ")" & Format(Now, " dd-mm-yy hh-mm-ss") &
".xls"

 

[Ed Davis]

I am not having any luck with this so I will await your response.
But I did notice one thing.
My personal file is also being saved under \Loja\Loja Normal Close even when
I do not access that book.
Is there a way to either disregard the personal book, or just close it with
save changes.

 

[JLatham]

Ed,
Getting very confusing in here. The ThisWorkbook. reference should have
been WBook. in the middle of that loop. "ThisWorkbook" is the master
workbook. WBook is some other workbook that is also open.

Find() doesn't work in VBA. the INSTR() function is used in VBA for that
type of thing. Explain exactly how you're trying to break up the workbook
name: Give an example of what one would look like, and what you want to get
out of it.

As for not saving the Personal.xls workbook;
Change

If WBook.Name <> ThisWorkbook.Name Then

to

If WBook.Name <> ThisWorkbook.Name And _
WBook.Name <> "Personal.xls" Then

to take care of that issue.

If you need, or want, you can get in touch with me on this matter at (remove
spaces)
Help From @ JLatham Site. com

 

[Ed Davis]

I am looking to get the following in a save as
Workbook.name in this case is Sales 09-2009
\Backup\Sales\Sales Normal Close 24-09-2009 12-25-34
The date and time would come from the now function

Workbook path & "\Backup\WBName\WBName Normal Close dd-mm-yyyy hh-mm-ss"
The "Backup" Directory would be the same for all books then a sub directory
for the \WBName\ then the WBname and "Normal Close etc:

I hope this explains it better.