Workbook doesn't fully close - sorta

[Mark]

Hi,
I’m trying to “fully†close a workbook via VBA in XL 2007 and having some
problems with it. Specifically, the Project window in the VBE still shows the
workbook. What I’m doing is creating a workbook based on a template and when
the User is done they simply “X†out of it and all of it works correctly,
except for what remains in the VBE. I can re-open and manipulate the workbook
created from the template, but the VBE still shows it. The code for the
template (ThisWorkbook) is:

Sub Workbook_BeforeClose(Cancel As Boolean)
Run "Personal.xls!EndIt", Cancel
End Sub

The Personal.xls code, in a standard module, is:

Sub EndIt(Cancel As Boolean)
ChDir extPath & grade
ActiveWorkbook.SaveAs extPath & grade & "\" & fName, FileFormat:=52
ActiveWorkbook.Close
Application.Caption = Empty
End Sub

At the time the code reaches the BeforeClose event, the following
conditions of the workbook are:

Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

There are no additional add-ins and it doesn’t seem to cause any problems,
however, from time to time, Excel does abend and wants me to send an error
report. Does anyone know why the workbook I create from the template appears
to close correctly in Excel, but not in the VBE Project window and what I
have to do to fix it?
Thanks.
Mark

 

[Patrick Molloy]

remove this line

ActiveWorkbook.Close

from the EndIt procedure --- you're already closing the workbook, as its
that workbooks beforeclose event that fires off.

 

[Mark]

Thanks, Patrick. I removed that line and tried it again, but I still have the
workbook showing in the Project window. It shows VBAProject (xxxxx.xlsm) and
directly below it VBAProject (Personal.xls)

 

[Tim Zych]

Do you have a variable which is referencing the workbook? If so, it should
be set to Nothing after you close the workbook. Here is a macro that
demonstrates similar behavior you see:

1. Create a workbook called A.xls. Add a module with some code.

2. Create a 2nd workbook, add a module and run the following macro:

Public wkb As Workbook
Sub Test()
Set wkb = Workbooks("A.xls")
End Sub

3. Close A.xls

4. Go back to the VBE and A.xls is still there and the code is visible too.

5. Click the Stop button. A.xls is cleared out.

Now perform the steps again with a modified macro, and omit Step 5.

Sub Test()
Set wkb = Workbooks("A.xls")
Set wkb = Nothing
End Sub

A.xls is no longer available.

--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

http://www.higherdata.com/sql/batchsqlfromexcel.html
Create batch SQL from Excel

 

[Mark]

Thanks, Tim. I went through the code and everywhere that I referenced a
workbook through a variable, I reset to "Nothing." However, I still have the
same problem. When I click on 'File', in the VBE, the first item in the list
is wanting me to save the workbook (xxxxx.xlsm) created via the template
(*.xltm).
Aside from the template extension, the only thing that I can think of that I
have done differently with this set of templates is adding a command button
(ActiveX) to the sheets, instead of adding options to the QAT or the ribbon.
Each command button simply unhides the next sheet in the workbook.
If I use a template with an extension of .xlt, then I don't seem to have the
problem. However, I was under the impression that the proper extension for
the templates was .xltm, since they contained macros. Am I making a mistake