automatically close second workbook
 

i have put the following code in to workbook 'A' to close workbook 'B' but it
does not close book 'B' there is no error message

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Workbooks("B.XLS").Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=True
End Sub

automatically close second workbook
 

Several of us have tested your code..........both workbook_open which opens
B.xls when A.xls opens and the workbook_beforeclose code.

Works fine for me.

All I can think of is that you are opening B.xls in a separate instance of
Excel.

If that's the case, the beforeclose code in A.xls will not trigger B.xls to
close.

Try this code in A.xls

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Workbooks("B.XLS").Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=True
Application.Quit
End Sub

Do you still have workbook B.xls open in an instance of Excel?


Gord Dibben MS Excel MVP

On Wed, 16 Dec 2009 12:29:02 -0800, uk
wrote:

i have put the following code in to workbook 'A' to close workbook 'B' but it
does not close book 'B' there is no error message

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Workbooks("B.XLS").Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=True
End Sub

automatically close second workbook
 

Gord,

The last time this poster had problems like this, it was because the extension
of the file wasn't .xls. It was .xlsm or .xlsx or .xlsb or who knows what.



Gord Dibben wrote:

Several of us have tested your code..........both workbook_open which opens
B.xls when A.xls opens and the workbook_beforeclose code.

Works fine for me.

All I can think of is that you are opening B.xls in a separate instance of
Excel.

If that's the case, the beforeclose code in A.xls will not trigger B.xls to
close.

Try this code in A.xls

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Workbooks("B.XLS").Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=True
Application.Quit
End Sub

Do you still have workbook B.xls open in an instance of Excel?

Gord Dibben MS Excel MVP

On Wed, 16 Dec 2009 12:29:02 -0800, uk
wrote:

i have put the following code in to workbook 'A' to close workbook 'B' but it
does not close book 'B' there is no error message

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Workbooks("B.XLS").Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=True
End Sub

--

Dave Peterson

automatically close second workbook
 

I seem to remember something like that but OP did state at that time B.xls
did open when he opened A.xls

So..........maybe the extension was correct in the open code but not in the
beforeclose code?


Gord


On Wed, 16 Dec 2009 16:41:13 -0600, Dave Peterson
wrote:

Gord,

The last time this poster had problems like this, it was because the extension
of the file wasn't .xls. It was .xlsm or .xlsx or .xlsb or who knows what.



Gord Dibben wrote:

Several of us have tested your code..........both workbook_open which opens
B.xls when A.xls opens and the workbook_beforeclose code.

Works fine for me.

All I can think of is that you are opening B.xls in a separate instance of
Excel.

If that's the case, the beforeclose code in A.xls will not trigger B.xls to
close.

Try this code in A.xls

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Workbooks("B.XLS").Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=True
Application.Quit
End Sub

Do you still have workbook B.xls open in an instance of Excel?

Gord Dibben MS Excel MVP

On Wed, 16 Dec 2009 12:29:02 -0800, uk
wrote:

i have put the following code in to workbook 'A' to close workbook 'B' but it
does not close book 'B' there is no error message

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Workbooks("B.XLS").Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=True
End Sub

automatically close second workbook
 

Or he didn't share the details of the changes that made it work.

(Just my bet.)

Gord Dibben wrote:

I seem to remember something like that but OP did state at that time B.xls
did open when he opened A.xls

So..........maybe the extension was correct in the open code but not in the
beforeclose code?

Gord

On Wed, 16 Dec 2009 16:41:13 -0600, Dave Peterson
wrote:

Gord,

The last time this poster had problems like this, it was because the extension
of the file wasn't .xls. It was .xlsm or .xlsx or .xlsb or who knows what.



Gord Dibben wrote:

Several of us have tested your code..........both workbook_open which opens
B.xls when A.xls opens and the workbook_beforeclose code.

Works fine for me.

All I can think of is that you are opening B.xls in a separate instance of
Excel.

If that's the case, the beforeclose code in A.xls will not trigger B.xls to
close.

Try this code in A.xls

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Workbooks("B.XLS").Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=True
Application.Quit
End Sub

Do you still have workbook B.xls open in an instance of Excel?

Gord Dibben MS Excel MVP

On Wed, 16 Dec 2009 12:29:02 -0800, uk
wrote:

i have put the following code in to workbook 'A' to close workbook 'B' but it
does not close book 'B' there is no error message

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Workbooks("B.XLS").Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=True
End Sub

--

Dave Peterson