Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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").Activat e
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



  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,163
Default Closing Hidden Workbook when Active Workbook is Closed

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").Activat e
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



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Closing Hidden Workbook when Active Workbook is Closed

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").Activat e
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



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Closing Hidden Workbook when Active Workbook is Closed

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").Activat e
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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Closing Hidden Workbook when Active Workbook is Closed

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").Activat e
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



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Closing Hidden Workbook when Active Workbook is Closed

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").Activat e
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Consolidation of data from cell in active sheet of closed workbook Neil X Peel Excel Worksheet Functions 3 March 8th 07 03:35 PM
Error on closing a hidden workbook BeSmart Excel Programming 3 September 23rd 05 01:41 AM
Closing a workbook and printing a non-active page Sleeping Bear[_2_] Excel Programming 4 August 5th 05 01:47 PM
hidden names in active workbook hamcdo Excel Worksheet Functions 2 June 1st 05 05:46 PM
copy worksheet from closed workbook to active workbook using vba mango Excel Worksheet Functions 6 December 9th 04 08:55 AM


All times are GMT +1. The time now is 01:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"