Pass control of code from one workbook to another 1

[cardiac]

I am running VBA code from an Excel workbook(wkb1) which then uses the code 'Application.run("'" & WorkbookName & "'!Auto_open") to start running the Auto_open routine in another workbook (wkb2).
Within this routine wkb1 is closed. However, on closing wkb1; the code from the currently active workbook (wkb2) stops running.

How can i get it to continue with the rest of the Auto_open routine?

 

[SkipVought]

Hi,

Have you searched this topic? This question was recently answered in this forum.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[PHV]

Have you tried this ?
wkb2.RunAutoMacros xlAutoOpen

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cardiac]

1. Skipvought - Yes I have searched the forum - but i could only find suggestions to run code in another workbook then return to the original workbooks code - without closing it.

2. PHV - thanks for that but it has exactly the same effect as Application.run - as soon as the 1st workbook closes then an exit sub seems to occur without completing the code beneath it.

 

[Zathras]

Assuming you have control over the code in both workbooks, here is one way to do what you are asking for.

Put this in the workbook code for Book2:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  On Error Resume Next
  Workbooks("Book1.xls").Sheets(1).Range("A1") = "xx"
  Workbooks("Book1.xls").RunAutoMacros xlAutoOpen
End Sub

Put this in a code module for Book1:

Sub Auto_Open()
  With Sheets(1)
    If Range("A1") = "xx" Then
      MsgBox "XX"
      Range("A1") = ""
    End If
  End With
End Sub

Choose some unused cell for the "sentinel" to indicate that the Autpen was invoked from somewhere other than the normal auto open sequence. I used Cell A1 in the example.

Split you macro into two parts. End the first part with opening Book2. Begin the second part with where you want to resume after closing Book2. Call the second routine from the Autpen macro (where I have stubbed the MsgBox call).

It's a bit of a kludge, but it should work well enough for you. The error trapping around the code in the BeforeClose macro is there in case Book1 is closed before Book2.

 

[cardiac]

Thanks Zathras - but it's book1 that opens book2; then book2 closes book1.