File won't close when "beforeclose" event is used.

  • Thread starter Thread starter David G
  • Start date Start date
D

David G

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.
 
It depends on what the code in the BeforeClose event is. If Cancel is being
set to True, the file won't close.
 
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
 
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.
 
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.
 
Back
Top