Transfer Control between workbooks

[donjohnson]

Hello!
I have a driver workbook with an application main menu. Once the user has selected their function and been validated, I would like to transfer control to another workbook I will open, and then close the main menu workbook for others to use (thus reducing update contention issues).

I can open the second workbook (actually a new workbook from a template), but how do I pass control to a macro there, and close the workbook I started from?

Thanks!

Don

 

[VBAjedi]

Don,

Have the LAST step of the code in Workbook 1 open Workbook 2. Then have code in the Workbook_Open event (or in an Autpen sub) of Workbook 2 take over, closing Workbook 1 and doing whatever else it needs to do.

VBAjedi

 

[donjohnson]

Thanks VBAJedi; now how would I handle it if I have to do a WB.Add, with filename, title etc.?

I have it this way:

strDir = Application.DefaultFilePath
strFile = strDir & "\" & VBA.Environ("USERNAME") & ".xls"
If FileExists(strFile) Then
    Set oDataWB = Workbooks.Open(strFile)
    MsgBox strFile & " exists"
Else
    Set oDataWB = Workbooks.Add(strDir & "\DPRDataTemplate.xlt")
    With oDataWB
        .Title = "Production Reporting Workbook"
        .SaveAs Filename:=strFile
    End With
End If

in a common subroutine.

Will I get into trouble because I am not in my main subroutine?

Don

 

[donjohnson]

I have hit an impasse with the transfer; I can open a new workbook, and close the old, but can't continue with the script in the new book to fire the new forms.

Unfortunately, the driver sheet is 77K, and the child process is over 150K with two forms. How can I have someone look at the code to help me? B-(

Thanks!

Don

 

[cardiac]

did you find a solution to this - i have the same problem - on closing the first workbook the code running in Auto_open of the second workbook stops!

 

[vbap]

You need the .RunAutoMacros method when you open the 2nd workbook containing an auto open routine.

Try this to open the 2nd workbook.

Workbooks.Open(Filename:="SayHello.xls").RunAutoMacros Which:=xlAutoOpen

ThisWorkbook.close SaveChanges:=False