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


--
Thank You in Advance
Ed Davis

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" wrote:

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


--
Thank You in Advance
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

--
Thank You in Advance
Ed Davis
"JLatham" wrote in message
...
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" wrote:

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


--
Thank You in Advance
Ed Davis

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" wrote:

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

--
Thank You in Advance
Ed Davis
"JLatham" wrote in message
...
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" wrote:

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


--
Thank You in Advance
Ed Davis

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



--
Thank You in Advance
Ed Davis
"JLatham" wrote in message
...
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" wrote:

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

--
Thank You in Advance
Ed Davis
"JLatham" wrote in message
...
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" wrote:

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


--
Thank You in Advance
Ed Davis

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" wrote:

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


--
Thank You in Advance
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.


--
Thank You in Advance
Ed Davis
"JLatham" wrote in message
...
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" wrote:

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


--
Thank You in Advance
Ed Davis

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" wrote:

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.


--
Thank You in Advance
Ed Davis
"JLatham" wrote in message
...
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" wrote:

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


--
Thank You in Advance
Ed Davis

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


--
Thank You in Advance
Ed Davis
"JLatham" wrote in message
...
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" wrote:

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.


--
Thank You in Advance
Ed Davis
"JLatham" wrote in message
...
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" wrote:

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


--
Thank You in Advance
Ed Davis

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" wrote:

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


--
Thank You in Advance
Ed Davis
"JLatham" wrote in message
...
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" wrote:

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.


--
Thank You in Advance
Ed Davis
"JLatham" wrote in message
...
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" wrote:

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


--
Thank You in Advance
Ed Davis