Excel crashes when using Workbook

Excel crashes when using Workbook_BeforeClose

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Torsten Hannerfeldt - 08 Jan 2004 08:39 GMT

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

Reply to this Message

Jan Karel Pieterse - 08 Jan 2004 09:22 GMT

Hi Torsten,

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

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

Reply to this Message

- 08 Jan 2004 10:53 GMT

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

>-----Original Message-----
>Hi Torsten,
[quoted text clipped - 20 lines]
>
>.

Reply to this Message

Jan Karel Pieterse - 08 Jan 2004 12:01 GMT

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

> 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
[quoted text clipped - 15 lines]
> I have a static variable tha exits the procedure if it is
> already running.

Reply to this Message