Problem with ActiveWorkbook.Close command

[Rob]

I have two workbooks; Test Menu.xlsm and TestWB.xlsm both residing in
the C:\Data folder

The Test Menu.xlsm file has two command buttons (from Form Controls).
The first button 'OpenTestWB' opens the TestWB file, the second button
'CloseThisWB2' closes the Test Menu.file.

The TestWB.xlsm file has one command button (also from Form Controls)
which simply closes the TestWB file, 'CloseThisWB1' and returns to
Test Menu.xlsm.

The OpenTestWB code works fine as does the CloseThisWB1

The problem occurs for some users when they click on the
'CloseThisWB2' button on the Test Menu.xlsm which results in a
Microsoft Windows error message. However for others, clicking this
button does what it's supposed to, that is it closes the workbook.
Furthermore for those users who were getting the error message, if
they had another Excel file open when they clicked the CloseThisWB2'
button on the Test Menu.xlsm it worked fine.

To remedy the situation I replaced the ActiveWorkbook.close code on
Test Menu.xlsm to read Application.Quit.

Problem solved, but it is still a mystery I'd like to solve and I
appreciate any ideas you have or if you spot something in my code.
Our company uses Excel 2007 on Vista

Here's the code for both these test workbooks:

Test Menu.xlsm:
Sub OpenTestWB()
Dim Testwb As Workbook
Set Testwb = Workbooks.Open("C:\Data\TestWb.xlsm")
End Sub

Sub CloseThisWB2()
Application.DisplayAlerts = False
ActiveWorkbook.Close
End Sub


TestWB.xlsm:
Sub CloseThisWB1()
Application.DisplayAlerts = False
ActiveWorkbook.Close
End Sub



Thank you,

Rob

 

[GS]

Try...

Test Menu.xlsm:

Sub OpenTestWB()
Workbooks.Open("C:\Data\TestWb.xlsm")
End Sub

Sub CloseThisWB2()
ThisWorkbook.Close SaveChanges:=False
End Sub

TestWB.xlsm:

Sub CloseThisWB1()
ThisWorkbook.Close SaveChanges:=False
End Sub

 

[Rob]

Try...


Sub OpenTestWB()
  Workbooks.Open("C:\Data\TestWb.xlsm")
End Sub

Sub CloseThisWB2()
  ThisWorkbook.Close SaveChanges:=False
End Sub


Sub CloseThisWB1()
  ThisWorkbook.Close SaveChanges:=False
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

Thank you this worked, but I'd like to understand why my original code
works on some machines and not others.

Thanks again for your help.

Rob

 

[GS]

Rob explained on 7/18/2011 :

Garry,

Thank you this worked, but I'd like to understand why my original code
works on some machines and not others.

Thanks again for your help.

Rob

You're welcome, Rob!

Note that...

'ThisWorkbook' is an EXPLICIT (fully qualified) reference to the file
containing the currently running code.

'ActiveWorkbook' is an IMPLICIT reference to whatever workbook belongs
to the currently active window.

'Application.Quit' shuts down Excel. I don't think this is what you
want to do.

'Application.DisplayAlerts = False' ALWAYS NEEDS TO BE TURNED BACK
ON!!! Thus, should only be used to temporarily cancel unwanted messages
while certain things are being done via code which would otherwise
generate an alert. Example...

Sub DoStuff()
With Application
.ScreenUpdating = False
vCalcMode = .Calculation
.Calulation = xlCalulationManual
End With

'Do stuff...

Application.DisplayAlerts = False
'Do stuff that raises an alert you want to cancel...
Application.DisplayAlerts = True

'Do more stuff...

'Cleanup
With Application
.ScreenUpdating = True
.Calculation = vCalcMode
End With
End Sub