Error on closing a hidden workbook
 

Hi all
I have an excel template that sources it's validation lists from a separate
workbook ("Masterlist.xls") which automatically opens and hides when the
template is opened. eg:
==============================================
Sub auto_open()
Application.ScreenUpdating = False
Workbooks.Open ("D:\Masterlist.xls")
ActiveWindow.Visible = False
Application.ScreenUpdating = True
End Sub
==============================================

I now need a "BeforeClose" code to automatically close the hidden workbook
"Masterlist.xls" (without saving changes), when the template is closed.

I tried this code, but I got a Compile error "Sub or Function not defined"
msg on the row marked with the below.
============================================
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Schedule").Select
Range("a1").Select
Workbook("MasterList.xls").Visible = True
ActiveWindow.Close False
End Sub
=======================================
Thank for your help
BeSmart

Error on closing a hidden workbook
 

Replace this:

Workbook("MasterList.xls").Visible = True
ActiveWindow.Close False

which is incorrect because it should be "Workbooks", not "Workbook" with
this:

Workbooks("MasterList.xls").Close False

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"BeSmart" wrote in message
...
Hi all
I have an excel template that sources it's validation lists from a
separate
workbook ("Masterlist.xls") which automatically opens and hides when the
template is opened. eg:
==============================================
Sub auto_open()
Application.ScreenUpdating = False
Workbooks.Open ("D:\Masterlist.xls")
ActiveWindow.Visible = False
Application.ScreenUpdating = True
End Sub
==============================================

I now need a "BeforeClose" code to automatically close the hidden workbook
"Masterlist.xls" (without saving changes), when the template is closed.

I tried this code, but I got a Compile error "Sub or Function not defined"
msg on the row marked with the below.
============================================
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Schedule").Select
Range("a1").Select
Workbook("MasterList.xls").Visible = True
ActiveWindow.Close False
End Sub
=======================================
Thank for your help
BeSmart

Error on closing a hidden workbook
 

Thanks Rob - too easy....
BeSmart

Error on closing a hidden workbook
 

Hello,
I have a similar set-up;
Workbook ("Data.xls") is a shared workbook that opens ("View.xls") via
the following:

Private Sub Workbook_Open()
Workbooks.Open Filename:="(full path to workbook View.xls)",
updateLinks:=0
ActiveWorkbook.UpdateRemoteReferences = False
Workbooks("View.xls").Worksheets("Menu").Activate
Windows("Data.xls").Activate
ActiveWindow.Visible = False
End Sub

Data.xls remains hidden except when posted to via macros in View.xls.
View.xls is linked to Data.xls but the link is updated only manually.

All of that works like a charm, my problems come in trying to get both
workbooks to close, without saving either, when the user closes
View.xls.

Currently, I have the following in View.xls:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Workbooks("GuestData.xls").Close False
ActiveWorkbook.Close False
End Sub

Which gives me - Run-time error 9, subscript out of range.

I have tried so many variations and am at my (very limited) wits end.
Any help would be greatly appreciated.
Thanks,
Susan
Excel 2000, Windows xp


--
K521
------------------------------------------------------------------------
K521's Profile: http://www.excelforum.com/member.php...o&userid=27495
View this thread: http://www.excelforum.com/showthread...hreadid=261490