Before_Close problem

[Roberto]

Hi

Before a workbook is closed I want to give user the option
of opening another one.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
if Msgbox("Do you need to open 'Data2.xls'", vbYesNo) =
vbYes then
workbooks.open "C:\Data\Date2.xls"
End if
End Sub

The problem is that while the second workbook Data2 is
opened ok, the original workbook which this code is in
does not close.
I don't understand this as it's in a BeforeClose sub
I've tried putting Else ThisWorkbook.close in, but this
causes the message box to be displayed all over again.

Please Help

 

[Chip Pearson]

Roberto,

I can confirm the behavior you describe. It looks like a bug to me. I can't
think of a good work around. Sorry.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Dave Peterson]

It seemed to run ok for me in xl2002 (SP2).

In fact, if the original workbook were dirty, then I'd even get prompted to
save.

So I'm guessing you're not using xl2002.

Maybe you can use application.ontime to call a macro that will close the
workbook.

This didn't work in xl2002--since the workbook was already closed and excel
reopened it to run the closeme sub:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Do you need to open 'Data2.xls'", vbYesNo) = vbYes Then
Application.OnTime Now + TimeSerial(0, 0, 5), "CloseMe"
Workbooks.Open "C:\my documents\excel\book1.xls"
End If
End Sub

And in a general module:

Option Explicit
Sub closeme()
ThisWorkbook.Close savechanges:=True
End Sub

 

[Roberto]

Hi

No, I'm using Excel 97
Even if I don't have the message box and just put the
Workbook.Open bit, the original workbook remains open.

Maybe it is a bug then, if it works for you?

 

[Sandy V]

Roberto,

You code works fine for me in XL97(SP1), ie Date2.xls
opens and/or is activated then the code file closes.

Perhaps not a version issue but something else in common
between your & Chip's setups but not Dave's & mine, but I
can't imagine what.

A slight variation on Dave's code with Cancel and a flag:

Option Explicit
Private CloseFlag As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not CloseFlag Then
If MsgBox("Do you need to open 'Data2.xls'", _
vbYesNo) = vbYes Then
Cancel = True
Workbooks.Open "C:\Temp\Data2.xls"
'Application.OnTime Now + TimeSerial(0, 0,
5), "CloseMe"
Application.OnTime Now, "CloseMe"
CloseFlag = True
End If
'Else
'MsgBox "Now closing"
End If
End Sub

And in a normal module

Option Explicit
Sub CloseMe()
ThisWorkbook.Close savechanges:=True
End Sub

I didn't need to delay the OnTime macro, but then I didn't
have a 'problem' so you might.

Regards,
Sandy
savituk yahoo co uk