Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with ActiveWorkbook.Close command
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with ActiveWorkbook.Close command
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with ActiveWorkbook.Close command
On Jul 18, 10:33*am, GS wrote:
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 -- 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with ActiveWorkbook.Close command
Rob explained on 7/18/2011 :
On Jul 18, 10:33*am, GS wrote: 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 -- 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 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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveWorkbook.close problem | Excel Programming | |||
ActiveWorkbook.close problem | Excel Programming | |||
In Before Close Sub ActiveWorkBook.Close(False) repeat procedure | Excel Programming | |||
Problem with Activeworkbook.Close SaveChanges:=False | Excel Programming | |||
ActiveWorkbook.Close | Excel Programming |