Excel Closing Problem

H

Him_B

Dear those Excel Experts,

I have a problem related to the excel application closing
problem. I have created a excel filewith 4 userforms, 4 worksheets and some
Marco on it. If I close the file but not to quit the excel application, the
VBAProject are still been seen in the VBE. I open the file again in this
circumstance, the Marco can not run properly. In other case, if I quit the
excel application when I close the file, It will have no problem. Can
anybody tell me why and how to solve it.
Many Thanks,
Kan
 
R

Ron de Bruin

Hi Kan

If you Dim outside your macro like this at the
top of your module

Dim WB As Workbook

Sub test()
Set WB = Workbooks.Open("c:\data\ron.xls")
WB.Close False
End Sub

The VBAProject are still been seen in the VBE
Use this then

Sub test()
Set WB = Workbooks.Open("c:\data\ron.xls")
WB.Close False
Set WB = Nothing
End Sub

Or Dim in the macro
 
M

Mike Fogleman

Without seeing the code I would guess that a userform is still loaded into
memory. Make sure all forms are Unloaded after they are hidden. If a form
needs to remain loaded into memory so subsequent code can use its properties
or values, then perhaps Unload the form in ThisWorkbook Before_Close event.

Unload UserForm1

If this is not seemingly the problem, post your code for more help.
Mike
 
H

Him_B

Ron de Bruin and Mike,
Both of your methods are in vain. However, I use the excel file
in other MS office computer but the problem is gone. I suppose that's the
problem in Excel. Hence I re-install MS office in my computer, the problem
disappear.
Anyway, Thanks again for your prompt reply.
Kan
 
Top