You may be able to work with the following code I posted
here. I have a hunch that you will need to avoid modal userforms. This code forces all other workbooks to be opened within a separate instance of Excel. Another way of stating this is that this code, pasted into ThisWorkbook class, will keep thisworkbook opened, alone, within it's own instance.
Tx. Provide some more details. When you mention other workbooks opening and interfering, are these workbooks opened via code from within the invisible instance or opened from some user from elsewhere? I cannot reproduce your problem. You will have to walk me through it.
<table width="100%" border="1" bgcolor="White" style="filter
rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New> <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> SetForegroundWindow <font color="#0000A0">Lib</font> "user32" _
(ByVal hWnd <font color="#0000A0">As</font> Long) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> FindWindow <font color="#0000A0">Lib</font> "user32" <font color="#0000A0">Alias</font> "FindWindowA" _
(ByVal lpClassName <font color="#0000A0">As</font> String, <font color="#0000A0">ByVal</font> lpWindowName <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">Private</font> <font color="#0000A0">WithEvents</font> pParentApp <font color="#0000A0">As</font> Application
<font color="#0000A0">Private</font> pAuxiliaryInstance <font color="#0000A0">As</font> Application
<font color="#0000A0">Private</font> pPersonalXls <font color="#0000A0">As</font> <font color="#0000A0">String</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Workbook_Open()
<font color="#0000A0">If</font> ThisWorkbook.ReadOnly <font color="#0000A0">Then</font> ThisWorkbook.Close <font color="#0000A0">False</font>
SetUpOrpan
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> SetUpOrpan()
<font color="#0000A0">Dim</font> wb <font color="#0000A0">As</font> Workbook
<font color="#008000"> 'get an instance to "PERSONAL.XLS" if it is loaded</font>
<font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">Resume</font> <font color="#0000A0">Next</font>
<font color="#0000A0">Set</font> wb = Workbooks("PERSONAL.XLS")
<font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">GoTo</font> 0
<font color="#0000A0">If</font> Workbooks.Count > 1 <font color="#0000A0">And</font> wb <font color="#0000A0">Is</font> <font color="#0000A0">Nothing</font> <font color="#0000A0">Then</font>
<font color="#008000"> 'PERSONAL.XLS not loaded</font>
<font color="#008000"> 'other workbooks are already open in this instance</font>
<font color="#008000"> 'open ThisWorkbook in a new instance of Excel.Application</font>
CloseMeAndStartMeInNewInstance
<font color="#0000A0">ElseIf</font> Workbooks.Count > 2 <font color="#0000A0">Then</font>
<font color="#008000"> 'PERSONAL.XLS may or may not be loaded. Does not matter at this point</font>
<font color="#008000"> 'other workbooks are already open in this instance</font>
<font color="#008000"> 'open ThisWorkbook in a new instance of Excel.Application</font>
CloseMeAndStartMeInNewInstance
<font color="#0000A0">Else</font>
<font color="#008000"> 'this instance is ok to open ThisWorkbook</font>
<font color="#008000"> 'close PERSONAL.XLS in this instance</font>
<font color="#0000A0">If</font> <font color="#0000A0">Not</font> wb <font color="#0000A0">Is</font> <font color="#0000A0">Nothing</font> <font color="#0000A0">Then</font>
pPersonalXls = wb.FullName
wb.Close
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#008000"> 'continue opening in this instance</font>
<font color="#008000"> 'start watching the application level events to</font>
<font color="#008000"> 'respond to any attempts to create a new workbook</font>
<font color="#008000"> 'or to open an existing workbook</font>
<font color="#0000A0">Set</font> pParentApp = Application
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> SetAuxiliaryInstance(App <font color="#0000A0">As</font> Excel.Application)
<font color="#0000A0">If</font> pAuxiliaryInstance <font color="#0000A0">Is</font> <font color="#0000A0">Nothing</font> <font color="#0000A0">Then</font>
<font color="#0000A0">Set</font> pAuxiliaryInstance = App
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Workbook_BeforeClose(Cancel <font color="#0000A0">As</font> Boolean)
<font color="#0000A0">If</font> <font color="#0000A0">Not</font> pAuxiliaryInstance <font color="#0000A0">Is</font> <font color="#0000A0">Nothing</font> <font color="#0000A0">Then</font>
<font color="#008000"> 'if the user closes the Auxiliary Instance but we still have a reference</font>
<font color="#008000"> 'to the application, we will have an orphaned proccess running invisibly</font>
<font color="#008000"> 'this will close it</font>
<font color="#0000A0">If</font> <font color="#0000A0">Not</font> pAuxiliaryInstance.Visible <font color="#0000A0">Then</font>
pAuxiliaryInstance.Quit
<font color="#0000A0">Set</font> pAuxiliaryInstance = <font color="#0000A0">Nothing</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> pParentApp_NewWorkbook(ByVal wb <font color="#0000A0">As</font> Workbook)
<font color="#008000"> 'close the newly created workbook and open it in the Auxiliary Instance</font>
wb.Close <font color="#0000A0">False</font>
CreateNewOrGetExistingInstance
pAuxiliaryInstance.Workbooks.Add
SetForegroundWindow FindWindow("XLMAIN", pAuxiliaryInstance.Caption)
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> pParentApp_WorkbookOpen(ByVal wb <font color="#0000A0">As</font> Workbook)
<font color="#008000"> 'close the newly opened workbook and re-open it in the Auxiliary Instance</font>
<font color="#0000A0">Dim</font> WorkbookFullName <font color="#0000A0">As</font> <font color="#0000A0">String</font>
<font color="#0000A0">If</font> wb.FullName = ThisWorkbook.FullName <font color="#0000A0">Then</font> <font color="#0000A0">Exit</font> <font color="#0000A0">Sub</font>
WorkbookFullName = wb.FullName
wb.Close <font color="#0000A0">False</font>
CreateNewOrGetExistingInstance
pAuxiliaryInstance.Workbooks.Open WorkbookFullName
SetForegroundWindow FindWindow("XLMAIN", pAuxiliaryInstance.Caption)
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> CreateNewOrGetExistingInstance()
<font color="#0000A0">If</font> pAuxiliaryInstance <font color="#0000A0">Is</font> <font color="#0000A0">Nothing</font> <font color="#0000A0">Then</font>
<font color="#0000A0">Set</font> pAuxiliaryInstance = <font color="#0000A0">New</font> Application
<font color="#0000A0">If</font> pAuxiliaryInstance.Workbooks.Count = 0 <font color="#0000A0">And</font> pPersonalXls <> "" <font color="#0000A0">Then</font>
pAuxiliaryInstance.Workbooks.Open pPersonalXls
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#008000"> 'always set the visiblility to True because the user may have closed</font>
<font color="#008000"> 'the application but it is still running invisibly if we have a reference to it</font>
pAuxiliaryInstance.Visible = <font color="#0000A0">True</font>
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> CloseMeAndStartMeInNewInstance()
<font color="#0000A0">Dim</font> xlApp <font color="#0000A0">As</font> Excel.Application
<font color="#008000"> 'Will "transfer" this workbook to another instance of Excel</font>
Application.DisplayAlerts = <font color="#0000A0">False</font>
ThisWorkbook.Saved = <font color="#0000A0">True</font>
<font color="#0000A0">If</font> ThisWorkbook.ReadOnly = <font color="#0000A0">False</font> <font color="#0000A0">Then</font>
ThisWorkbook.ChangeFileAccess xlReadOnly
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
Application.DisplayAlerts = <font color="#0000A0">True</font>
<font color="#0000A0">Set</font> xlApp = <font color="#0000A0">New</font> Excel.Application
xlApp.Workbooks.Open ThisWorkbook.FullName
xlApp.UserControl = <font color="#0000A0">True</font>
xlApp.Visible = <font color="#0000A0">True</font>
xlApp.Run ThisWorkbook.Name & "!Thisworkbook.SetAuxiliaryInstance", Application
ThisWorkbook.Close savechanges:=False
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("92120063722562").value=document.all("92120063722562").value.replace(/<br \/>\s\s/g,"");document.all("92120063722562").value=document.all("92120063722562").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("92120063722562").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="92120063722562" wrap="virtual">
Option Explicit
Private Declare Function SetForegroundWindow Lib "user32" _
(ByVal hWnd As Long) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private WithEvents pParentApp As Application
Private pAuxiliaryInstance As Application
Private pPersonalXls As String
Private Sub Workbook_Open()
If ThisWorkbook.ReadOnly Then ThisWorkbook.Close False
SetUpOrpan
End Sub
Private Sub SetUpOrpan()
Dim wb As Workbook
'get an instance to "PERSONAL.XLS" if it is loaded
On Error Resume Next
Set wb = Workbooks("PERSONAL.XLS")
On Error GoTo 0
If Workbooks.Count > 1 And wb Is Nothing Then
'PERSONAL.XLS not loaded
'other workbooks are already open in this instance
'open ThisWorkbook in a new instance of Excel.Application
CloseMeAndStartMeInNewInstance
ElseIf Workbooks.Count > 2 Then
'PERSONAL.XLS may or may not be loaded. Does not matter at this point
'other workbooks are already open in this instance
'open ThisWorkbook in a new instance of Excel.Application
CloseMeAndStartMeInNewInstance
Else
'this instance is ok to open ThisWorkbook
'close PERSONAL.XLS in this instance
If Not wb Is Nothing Then
pPersonalXls = wb.FullName
wb.Close
End If
'continue opening in this instance
'start watching the application level events to
'respond to any attempts to create a new workbook
'or to open an existing workbook
Set pParentApp = Application
End If
End Sub
Private Sub SetAuxiliaryInstance(App As Excel.Application)
If pAuxiliaryInstance Is Nothing Then
Set pAuxiliaryInstance = App
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not pAuxiliaryInstance Is Nothing Then
'if the user closes the Auxiliary Instance but we still have a reference
'to the application, we will have an orphaned proccess running invisibly
'this will close it
If Not pAuxiliaryInstance.Visible Then
pAuxiliaryInstance.Quit
Set pAuxiliaryInstance = Nothing
End If
End If
End Sub
Private Sub pParentApp_NewWorkbook(ByVal wb As Workbook)
'close the newly created workbook and open it in the Auxiliary Instance
wb.Close False
CreateNewOrGetExistingInstance
pAuxiliaryInstance.Workbooks.Add
SetForegroundWindow FindWindow("XLMAIN", pAuxiliaryInstance.Caption)
End Sub
Private Sub pParentApp_WorkbookOpen(ByVal wb As Workbook)
'close the newly opened workbook and re-open it in the Auxiliary Instance
Dim WorkbookFullName As String
If wb.FullName = ThisWorkbook.FullName Then Exit Sub
WorkbookFullName = wb.FullName
wb.Close False
CreateNewOrGetExistingInstance
pAuxiliaryInstance.Workbooks.Open WorkbookFullName
SetForegroundWindow FindWindow("XLMAIN", pAuxiliaryInstance.Caption)
End Sub
Private Sub CreateNewOrGetExistingInstance()
If pAuxiliaryInstance Is Nothing Then
Set pAuxiliaryInstance = New Application
If pAuxiliaryInstance.Workbooks.Count = 0 And pPersonalXls <> "" Then
pAuxiliaryInstance.Workbooks.Open pPersonalXls
End If
End If
'always set the visiblility to True because the user may have closed
'the application but it is still running invisibly if we have a reference to it
pAuxiliaryInstance.Visible = True
End Sub
Private Sub CloseMeAndStartMeInNewInstance()
Dim xlApp As Excel.Application
'Will "transfer" this workbook to another instance of Excel
Application.DisplayAlerts = False
ThisWorkbook.Saved = True
If ThisWorkbook.ReadOnly = False Then
ThisWorkbook.ChangeFileAccess xlReadOnly
End If
Application.DisplayAlerts = True
Set xlApp = New Excel.Application
xlApp.Workbooks.Open ThisWorkbook.FullName
xlApp.UserControl = True
xlApp.Visible = True
xlApp.Run ThisWorkbook.Name & "!Thisworkbook.SetAuxiliaryInstance", Application
ThisWorkbook.Close savechanges:=False
End Sub</textarea>