Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File won't close when "beforeclose" event is used.
I have an Excel file that opens up other files to
retreive info. Once the info is copied the files were to be closed using the .close method. I could not figure out why they wouldn't close. When I stepped through the VBA code the files would close, but not when the code was running by itself. I knew the files that I wanted to close contained code in the beforeclose event. Once I removed this code, the files closed as directed. Is there a way around this? I've changed the files so they no longer require code in the beforeclose event, but I would like to know if this is a bug or if I have to add something to allow code to be added to the beforeclose event. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File won't close when "beforeclose" event is used.
It depends on what the code in the BeforeClose event is. If Cancel is being
set to True, the file won't close. -- Vasant "David G" wrote in message ... I have an Excel file that opens up other files to retreive info. Once the info is copied the files were to be closed using the .close method. I could not figure out why they wouldn't close. When I stepped through the VBA code the files would close, but not when the code was running by itself. I knew the files that I wanted to close contained code in the beforeclose event. Once I removed this code, the files closed as directed. Is there a way around this? I've changed the files so they no longer require code in the beforeclose event, but I would like to know if this is a bug or if I have to add something to allow code to be added to the beforeclose event. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File won't close when "beforeclose" event is used.
Hi
I think this is a problem that has been mentioned before. If I remember correctly a public boolean variable was used as a kind of switch. eg Public CloseSwitch under the sub Workbook_Open set this to False in the beforeclose code, use a select case to set it to true if it's false. The Select Case stops the workbook being opened, being opened twice. Public CloseSwitch Sub Workbook_Open CloseSwitch = False end sub Sub Workbook_BeforeClose Select case CloseSwitch Case False CloseSwitch = True Workbooks.Open "file to be opened" ThisWorkbook.Close end select end sub -----Original Message----- I have an Excel file that opens up other files to retreive info. Once the info is copied the files were to be closed using the .close method. I could not figure out why they wouldn't close. When I stepped through the VBA code the files would close, but not when the code was running by itself. I knew the files that I wanted to close contained code in the beforeclose event. Once I removed this code, the files closed as directed. Is there a way around this? I've changed the files so they no longer require code in the beforeclose event, but I would like to know if this is a bug or if I have to add something to allow code to be added to the beforeclose event. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
File won't close when "beforeclose" event is used.
Sorry, Libby; but I'm not familiar with the use of this "CloseSwitch"
variable, so I don't think I'm understanding the issue here. If you don't get an answer in this thread, you might try reposting your question with more detail. -- Vasant "Libby" wrote in message ... Hi I think this is a problem that has been mentioned before. If I remember correctly a public boolean variable was used as a kind of switch. eg Public CloseSwitch under the sub Workbook_Open set this to False in the beforeclose code, use a select case to set it to true if it's false. The Select Case stops the workbook being opened, being opened twice. Public CloseSwitch Sub Workbook_Open CloseSwitch = False end sub Sub Workbook_BeforeClose Select case CloseSwitch Case False CloseSwitch = True Workbooks.Open "file to be opened" ThisWorkbook.Close end select end sub -----Original Message----- I have an Excel file that opens up other files to retreive info. Once the info is copied the files were to be closed using the .close method. I could not figure out why they wouldn't close. When I stepped through the VBA code the files would close, but not when the code was running by itself. I knew the files that I wanted to close contained code in the beforeclose event. Once I removed this code, the files closed as directed. Is there a way around this? I've changed the files so they no longer require code in the beforeclose event, but I would like to know if this is a bug or if I have to add something to allow code to be added to the beforeclose event. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
File won't close when "beforeclose" event is used.
If you wanted to open workbook B from workbook A and then
have workbook A close, the logical step would be to put Workbooks.Open"B" in the Before_Close event in workbook A so that workbook B would open and workbook A would close. However, this for some reason doesn't work and you end up with workbook A still open. The CloseSwitch variable in workbook A gets around this problem. Public CloseSwitch 'make it available to all procedures Sub Workbooks_Open CloseSwitch = False 'set it to false end sub Sub workbooks_BeforeClose Select case CloseSwitch Case False CloseSwitch = True Workbooks.Open "workbook B" ThisWorkbook.Close end select end sub When you close workbook A the BeforeClose sub is executed. Since we set CloseSwitch to false when we opened the workbook, the Case False is excecuted in the Select Case CloseSwitch. The select case is needed because when ThisWorkbook.Close is executed, the BeforeClose sub will be run again, only this time since we previously re-set CloseSwitch to True the workbook isn't opened a second time, nothing happens and workbook A closes leaving just workbook B. -----Original Message----- Sorry, Libby; but I'm not familiar with the use of this "CloseSwitch" variable, so I don't think I'm understanding the issue here. If you don't get an answer in this thread, you might try reposting your question with more detail. -- Vasant "Libby" wrote in message ... Hi I think this is a problem that has been mentioned before. If I remember correctly a public boolean variable was used as a kind of switch. eg Public CloseSwitch under the sub Workbook_Open set this to False in the beforeclose code, use a select case to set it to true if it's false. The Select Case stops the workbook being opened, being opened twice. Public CloseSwitch Sub Workbook_Open CloseSwitch = False end sub Sub Workbook_BeforeClose Select case CloseSwitch Case False CloseSwitch = True Workbooks.Open "file to be opened" ThisWorkbook.Close end select end sub -----Original Message----- I have an Excel file that opens up other files to retreive info. Once the info is copied the files were to be closed using the .close method. I could not figure out why they wouldn't close. When I stepped through the VBA code the files would close, but not when the code was running by itself. I knew the files that I wanted to close contained code in the beforeclose event. Once I removed this code, the files closed as directed. Is there a way around this? I've changed the files so they no longer require code in the beforeclose event, but I would like to know if this is a bug or if I have to add something to allow code to be added to the beforeclose event. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Lost "File Menu" - now it's "Edit / View / Insert.." but no "F | Excel Discussion (Misc queries) | |||
Disable "Save" &/or "Close" | Excel Programming | |||
How can I get "File Close" to prompt me to "Save Changes" ?? | Excel Programming |