Redirect Workbook Open

[bdmangum]

I have a workbook which needs to run in an instance of Excel by itself. The workbook runs entirely based upon forms with the application hidden throughout most of the functions of the workbook, thus my need for it to run in a seperate instance of Excel.

I've made it so the workbook always launches into a new application of Excel. The code below seems to take care of a user trying to load a new workbook or open a workbook while viewing a spreadsheet. My problem is allowing the user to open a workbook while the forms are running. Most of my users won't know they are using Excel due to te nature of the forms. I have designed it so it functions almost like a stand-alone program.

The problem is that when the forms are running the user cannot load another workbook. This is a problem as a lot of users will want to view other workbook while inputting data into the forms. Is there a way to redirect workbook open procedures while an Excel form is running in the active procedure? Or someone could also attempt to modify the below code to work for forms as well. The below code works great as long as there is not a form loaded.

Public WithEvents xlApp As Application
Public WithEvents thisApp As Application

Private Sub thisApp_NewWorkbook(ByVal Wb As Workbook)
    Wb.Close False
    Set Wb = xlApp.Workbooks.Add
    xlApp.Visible = True
    xlApp.Workbooks(Wb.Name).Activate
End Sub

Private Sub thisApp_WorkbookDeactivate(ByVal Wb As Workbook)
    If xlApp Is Nothing Then
        Set xlApp = New Application
    Else
        If xlApp.Workbooks.Count = 0 Then
            xlApp.Quit
            Set xlApp = Nothing
            Set xlApp = New Application
        End If
    End If
End Sub

Private Sub thisApp_WorkbookOpen(ByVal Wb As Workbook)
    If Not Wb Is ThisWorkbook Then
        wbkPath = Wb.FullName
        wbkName = Wb.Name
        Wb.Close False
        xlApp.Workbooks.Open wbkPath
        xlApp.Visible = True
        xlApp.Workbooks(wbkName).Activate
    End If
End Sub

Private Sub Workbook_Activate()
    If xlApp Is Nothing Then
        Set xlApp = New Application
    Else
        If xlApp.Workbooks.Count = 0 Then
            xlApp.Quit
            Set xlApp = Nothing
            Set xlApp = New Application
        End If
    End If
End Sub

Private Sub Workbook_Open()
    Set thisApp = Application
    Set xlApp = New Application     'Becomes activate instance, thus any attempted workbook open goes here
    xlApp.Visible = True
End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
    xlApp.Visible = True
End Sub

Thanks!
BD

 

[bdmangum]

It never fails. Soon after I submit a post with my problem I find the solution. For anyone interested, the solution is at this website:

http://www.ozgrid.com/forum/showthread.php?t=58697