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
|
Similar Threads
