Closing Hidden Workbook when Active Workbook is Closed

Data.xls is a shared workbook which, when opened, also opens View.xls via the
following:

Private Sub Workbook_Open()
Workbooks.Open Filename:="C:\My Documents\TestBook\View.xls", updateLinks:=0
ActiveWorkbook.UpdateRemoteReferences = False
Workbooks("View.xls").Worksheets("Sheet1").Activate
Windows("Data.xls").Activate
ActiveWindow.Visible = False
End Sub

Data.xls remains hidden to the user throughout the session.
View.xls has 6 protected worksheets, linked to Data.xls, and is nothing more
than a user interface, allowing the user to input to and sort the information
on Data. xls, all of which is done through macros.

All of the above works fine. The problem I'm having is finding a means of
closing Data.xls when the user closes View. xls.

Currently the following is in View.xls:

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

This results in Run-time error 9 Subscript out of range.

Both workbooks need to close, without saving, when View.xls is closed.
After numerous variations of activating & closing windows, workbooks, etc. I
am at my (admittedly limited) wits end.
Any help would be greatly appreciated.
Susan

Your trouble is in the line Workbooks("Data.xls").Close False and the problem
seems to be that it does not recognize Data.xls as the workbook name. Check
the name against what appears in the project explorer (VBA editor) when the
book is open. Or refer to it as Workbooks(2).
--
- K Dales


"SusanK521" wrote:

> Data.xls is a shared workbook which, when opened, also opens View.xls via the
> following:
>
> Private Sub Workbook_Open()
> Workbooks.Open Filename:="C:\My Documents\TestBook\View.xls", updateLinks:=0
> ActiveWorkbook.UpdateRemoteReferences = False
> Workbooks("View.xls").Worksheets("Sheet1").Activate
> Windows("Data.xls").Activate
> ActiveWindow.Visible = False
> End Sub
>
> Data.xls remains hidden to the user throughout the session.
> View.xls has 6 protected worksheets, linked to Data.xls, and is nothing more
> than a user interface, allowing the user to input to and sort the information
> on Data. xls, all of which is done through macros.
>
> All of the above works fine. The problem I'm having is finding a means of
> closing Data.xls when the user closes View. xls.
>
> Currently the following is in View.xls:
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Workbooks("Data.xls").Close False
> ActiveWorkbook.Close False
> End Sub
>
> This results in Run-time error 9 Subscript out of range.
>
> Both workbooks need to close, without saving, when View.xls is closed.
> After numerous variations of activating & closing windows, workbooks, etc. I
> am at my (admittedly limited) wits end.
> Any help would be greatly appreciated.
> Susan
>
>
>

Thank you for your suggestion. Referring to Workbooks(2) did remove the
run-time error, but did not close the workbook.

"K Dales" wrote:

> Your trouble is in the line Workbooks("Data.xls").Close False and the problem
> seems to be that it does not recognize Data.xls as the workbook name. Check
> the name against what appears in the project explorer (VBA editor) when the
> book is open. Or refer to it as Workbooks(2).
> --
> - K Dales
>
>
> "SusanK521" wrote:
>
> > Data.xls is a shared workbook which, when opened, also opens View.xls via the
> > following:
> >
> > Private Sub Workbook_Open()
> > Workbooks.Open Filename:="C:\My Documents\TestBook\View.xls", updateLinks:=0
> > ActiveWorkbook.UpdateRemoteReferences = False
> > Workbooks("View.xls").Worksheets("Sheet1").Activate
> > Windows("Data.xls").Activate
> > ActiveWindow.Visible = False
> > End Sub
> >
> > Data.xls remains hidden to the user throughout the session.
> > View.xls has 6 protected worksheets, linked to Data.xls, and is nothing more
> > than a user interface, allowing the user to input to and sort the information
> > on Data. xls, all of which is done through macros.
> >
> > All of the above works fine. The problem I'm having is finding a means of
> > closing Data.xls when the user closes View. xls.
> >
> > Currently the following is in View.xls:
> >
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > Workbooks("Data.xls").Close False
> > ActiveWorkbook.Close False
> > End Sub
> >
> > This results in Run-time error 9 Subscript out of range.
> >
> > Both workbooks need to close, without saving, when View.xls is closed.
> > After numerous variations of activating & closing windows, workbooks, etc. I
> > am at my (admittedly limited) wits end.
> > Any help would be greatly appreciated.
> > Susan
> >
> >
> >

Which line was causing the error?

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

When I tested, I had data.xls open as a hidden workbook and it worked fine.

But I think I'd change it slightly--just in case data.xls was already closed:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
on error resume next
Workbooks("Data.xls").Close False
on error goto 0

me.Close False

End Sub

Me refers to the object owning the code--in this case, it refers to the view.xls
workbook. (I'd be afraid that the wrong workbook was active.)

And how do you know that data.xls wasn't closed? Did you look at the VBE
(sometimes the project appears there--even after the workbook is closed) or did
you unhide the window to see?





SusanK521 wrote:
>
> Data.xls is a shared workbook which, when opened, also opens View.xls via the
> following:
>
> Private Sub Workbook_Open()
> Workbooks.Open Filename:="C:\My Documents\TestBook\View.xls", updateLinks:=0
> ActiveWorkbook.UpdateRemoteReferences = False
> Workbooks("View.xls").Worksheets("Sheet1").Activate
> Windows("Data.xls").Activate
> ActiveWindow.Visible = False
> End Sub
>
> Data.xls remains hidden to the user throughout the session.
> View.xls has 6 protected worksheets, linked to Data.xls, and is nothing more
> than a user interface, allowing the user to input to and sort the information
> on Data. xls, all of which is done through macros.
>
> All of the above works fine. The problem I'm having is finding a means of
> closing Data.xls when the user closes View. xls.
>
> Currently the following is in View.xls:
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Workbooks("Data.xls").Close False
> ActiveWorkbook.Close False
> End Sub
>
> This results in Run-time error 9 Subscript out of range.
>
> Both workbooks need to close, without saving, when View.xls is closed.
> After numerous variations of activating & closing windows, workbooks, etc. I
> am at my (admittedly limited) wits end.
> Any help would be greatly appreciated.
> Susan

--

Dave Peterson

Using your code worked! It closed both workbooks! Thank you so much!

Just to answer your question, though...
Workbooks("Data.xls").Close False
is the line that was causing the run-time error.

I did change it to Workbooks(2) as suggested earlier and I didn't get the
run-time error but it did not close Data.xls. I determined Data.xls was still
open by unhiding it.

Thanks again,
Susan

"Dave Peterson" wrote:

> Which line was causing the error?
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Workbooks("Data.xls").Close False
> ActiveWorkbook.Close False
> End Sub
>
> When I tested, I had data.xls open as a hidden workbook and it worked fine.
>
> But I think I'd change it slightly--just in case data.xls was already closed:
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> on error resume next
> Workbooks("Data.xls").Close False
> on error goto 0
>
> me.Close False
>
> End Sub
>
> Me refers to the object owning the code--in this case, it refers to the view.xls
> workbook. (I'd be afraid that the wrong workbook was active.)
>
> And how do you know that data.xls wasn't closed? Did you look at the VBE
> (sometimes the project appears there--even after the workbook is closed) or did
> you unhide the window to see?
>
>
>
>
>
> SusanK521 wrote:
> >
> > Data.xls is a shared workbook which, when opened, also opens View.xls via the
> > following:
> >
> > Private Sub Workbook_Open()
> > Workbooks.Open Filename:="C:\My Documents\TestBook\View.xls", updateLinks:=0
> > ActiveWorkbook.UpdateRemoteReferences = False
> > Workbooks("View.xls").Worksheets("Sheet1").Activate
> > Windows("Data.xls").Activate
> > ActiveWindow.Visible = False
> > End Sub
> >
> > Data.xls remains hidden to the user throughout the session.
> > View.xls has 6 protected worksheets, linked to Data.xls, and is nothing more
> > than a user interface, allowing the user to input to and sort the information
> > on Data. xls, all of which is done through macros.
> >
> > All of the above works fine. The problem I'm having is finding a means of
> > closing Data.xls when the user closes View. xls.
> >
> > Currently the following is in View.xls:
> >
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > Workbooks("Data.xls").Close False
> > ActiveWorkbook.Close False
> > End Sub
> >
> > This results in Run-time error 9 Subscript out of range.
> >
> > Both workbooks need to close, without saving, when View.xls is closed.
> > After numerous variations of activating & closing windows, workbooks, etc. I
> > am at my (admittedly limited) wits end.
> > Any help would be greatly appreciated.
> > Susan
>
> --
>
> Dave Peterson
>

Workbooks(2) may work if you lived a very saintly life--it would be a
coincidence (in my mind) that data.xls just happened to be workbooks(2).

I really don't see a big difference between your code and mine, though. Keep an
eye out for problems.

SusanK521 wrote:
>
> Using your code worked! It closed both workbooks! Thank you so much!
>
> Just to answer your question, though...
> Workbooks("Data.xls").Close False
> is the line that was causing the run-time error.
>
> I did change it to Workbooks(2) as suggested earlier and I didn't get the
> run-time error but it did not close Data.xls. I determined Data.xls was still
> open by unhiding it.
>
> Thanks again,
> Susan
>
> "Dave Peterson" wrote:
>
> > Which line was causing the error?
> >
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > Workbooks("Data.xls").Close False
> > ActiveWorkbook.Close False
> > End Sub
> >
> > When I tested, I had data.xls open as a hidden workbook and it worked fine.
> >
> > But I think I'd change it slightly--just in case data.xls was already closed:
> >
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > on error resume next
> > Workbooks("Data.xls").Close False
> > on error goto 0
> >
> > me.Close False
> >
> > End Sub
> >
> > Me refers to the object owning the code--in this case, it refers to the view.xls
> > workbook. (I'd be afraid that the wrong workbook was active.)
> >
> > And how do you know that data.xls wasn't closed? Did you look at the VBE
> > (sometimes the project appears there--even after the workbook is closed) or did
> > you unhide the window to see?
> >
> >
> >
> >
> >
> > SusanK521 wrote:
> > >
> > > Data.xls is a shared workbook which, when opened, also opens View.xls via the
> > > following:
> > >
> > > Private Sub Workbook_Open()
> > > Workbooks.Open Filename:="C:\My Documents\TestBook\View.xls", updateLinks:=0
> > > ActiveWorkbook.UpdateRemoteReferences = False
> > > Workbooks("View.xls").Worksheets("Sheet1").Activate
> > > Windows("Data.xls").Activate
> > > ActiveWindow.Visible = False
> > > End Sub
> > >
> > > Data.xls remains hidden to the user throughout the session.
> > > View.xls has 6 protected worksheets, linked to Data.xls, and is nothing more
> > > than a user interface, allowing the user to input to and sort the information
> > > on Data. xls, all of which is done through macros.
> > >
> > > All of the above works fine. The problem I'm having is finding a means of
> > > closing Data.xls when the user closes View. xls.
> > >
> > > Currently the following is in View.xls:
> > >
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > Workbooks("Data.xls").Close False
> > > ActiveWorkbook.Close False
> > > End Sub
> > >
> > > This results in Run-time error 9 Subscript out of range.
> > >
> > > Both workbooks need to close, without saving, when View.xls is closed.
> > > After numerous variations of activating & closing windows, workbooks, etc. I
> > > am at my (admittedly limited) wits end.
> > > Any help would be greatly appreciated.
> > > Susan
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson