Excel crashes when using Workbook_BeforeClose

  • Thread starter Thread starter Torsten Hannerfeldt
  • Start date Start date
T

Torsten Hannerfeldt

Hello!
I have a workbook where I don't want any changes to be
saved. (It is only used to communicate with a database).

I use the Workbook_BeforeClose to close the file without
saving. It works fine when only this workbook is open but
Excel crashes when there is another workbook(s) open.

The code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Close SaveChanges:=False
End Sub

Any suggestions?

/Torsten Hannerfeldt
 
Hi Torsten,
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Close SaveChanges:=False
End Sub
Click to expand...

This code causes the before_close event to fire over and over again
(try stepping through the code by putting a breakpoint on the me.close
line and trying to close the workbook).

Change it to:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved=True
End Sub


Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
Thanks for your answer Jan!

But, I did try the Me.Saved = True and it didn't solve
the problem. In fact my code was a bit longer than the
example i posted. Here is it in complete:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Static blnRunning As Boolean

If blnRunning = True Then Exit Sub

blnRunning = True

Me.Saved = True
Me.Activate
Me.Close SaveChanges:=False
blnRunning = False
End Sub

I have a static variable tha exits the procedure if it is
already running.

I also tried to include the Me.Activate to be shure the
workbook is activated.

Any suggestions?
/Torsten Hannerfeldt
 
Hi Thorsten,

Strange, the example I gave you should suffice (works fine for me, even
with 6 workbooks open:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
Back
Top