|
Workbook_BeforeClose
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 |
Workbook_BeforeClose
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 |
Workbook_BeforeClose
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 |
Workbook_BeforeClose
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 |
Workbook_BeforeClose
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 |
Workbook_BeforeClose
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 |
Workbook_BeforeClose
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 |
Workbook_BeforeClose
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 |
Workbook_BeforeClose
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 |
Workbook_BeforeClose
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 |
Workbook_BeforeClose
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. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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 |
Workbook_BeforeClose
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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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 |
Workbook_BeforeClose
I think I fixed the #2. from the previous post.
-- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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 |
Workbook_BeforeClose
#2. If working properly now.
The only issue is the master being saved 7 times and Loja not being saved. (as Normal Close) -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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 |
Workbook_BeforeClose
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" wrote: #2. If working properly now. The only issue is the master being saved 7 times and Loja not being saved. (as Normal Close) -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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 |
Workbook_BeforeClose
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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... #2. If working properly now. The only issue is the master being saved 7 times and Loja not being saved. (as Normal Close) -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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 |
Workbook_BeforeClose
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" -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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" wrote: #2. If working properly now. The only issue is the master being saved 7 times and Loja not being saved. (as Normal Close) -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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 |
Workbook_BeforeClose
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. -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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" -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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" wrote: #2. If working properly now. The only issue is the master being saved 7 times and Loja not being saved. (as Normal Close) -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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 |
Workbook_BeforeClose
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" wrote: 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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... #2. If working properly now. The only issue is the master being saved 7 times and Loja not being saved. (as Normal Close) -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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 |
Workbook_BeforeClose
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. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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" wrote: 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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... #2. If working properly now. The only issue is the master being saved 7 times and Loja not being saved. (as Normal Close) -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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 |
Workbook_BeforeClose
I was able to get everything working properly.
I used code that Dave Peterson gave me and everything is working the way that it should. Thank you so much for your time. This News group is the best. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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" wrote: 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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... #2. If working properly now. The only issue is the master being saved 7 times and Loja not being saved. (as Normal Close) -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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 |
Workbook_BeforeClose
That's great to hear!
"Ed Davis" wrote: I was able to get everything working properly. I used code that Dave Peterson gave me and everything is working the way that it should. Thank you so much for your time. This News group is the best. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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" wrote: 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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... #2. If working properly now. The only issue is the master being saved 7 times and Loja not being saved. (as Normal Close) -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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" -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... 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. |
| All times are GMT +1. The time now is 03:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com