Sub to check & force open Parent.xls

[Max]

I have 3 files in the same folder (for simplicity):
Parent.xls, Child1.xls, Child2.xls

Is there a sub I could place in Child1/2.xls which will automatically check
& if necessary "force" open Parent.xls first before Child1/2.xls itself
proceeds to open. I always want Parent.xls to be open before either
Child1.xls or Child2.xls is open. Thanks.

---

 

[Guest]

Create a fourth workbook that you ALWAYS open before openning any of the
other three. This workbook's sole function is to open the others in the
correct order.

 

[Chip Pearson]

Max,

Try something like the following. If Parent.xls is already open, it does
nothing. If Parent.xls is not open, it opens Parent.xls, schedules an
OnTime to cause Excel to re-open the Child.xls workbook, then closes itself.

Sub Auto_Open()
Dim WB As Workbook
On Error Resume Next
Set WB = Workbooks("Parent.xls")
Select Case Err.Number
Case 0
' Parent.xls already open
Case 9
' Parent.xls is not open
Workbooks.Open Filename:="C:\Parent.xls"
Application.OnTime Now, Chr(39) & ThisWorkbook.Name & Chr(39) &
"!DoNothing"
ThisWorkbook.Close savechanges:=True

Case Else
MsgBox "Error: " & CStr(Err.Number) & vbCrLf & _
Err.Description
End Select
End Sub


Sub DoNothing()
' no code required.
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

 

[Max]

Thanks, GS. It's a discipline to "enforce" onto users who forget to always
open Parent.xls ahead of opening Child1/2.xls, despite reminders. I'm not
sure that asking them to open yet another workbook? would help here. Anyway,
could you share some sample code for try-out?

 

[Max]

Thanks, Chip. Where should I place the subs, within either Child1.xls &
Child2.xls? Is it within ThisWorkbook? Pl advise.

 

[Guest]

Here is an example. We have a folder with four sheets:

master.xls
parent.xls
child1.xls
child2.xls

The user is told to ONLY open the master.

The master has the following in workbook code:

Private Sub Workbook_Open()
ChDir "C:\temp"
Workbooks.Open Filename:="C:\temp\parent.xls"
Workbooks.Open Filename:="C:\temp\child1.xls"
Workbooks.Open Filename:="C:\temp\child2.xls"
Windows("master.xls").Activate
ActiveWorkbook.Close
End Sub

So the master opens the working sheets in the proper order and then excuses
itself.

 

[Max]

GS, many thanks. Tried it and it works.

But I'm still hoping that there's a way to effect the force open Parent.xls
directly should the user "inadvertently" proceed to open Child1.xls,
notwithstanding the instructions given.