Don't allow another workbook to open in this instance?

[Rekd]

I have a workbook that opens from a script that creates a new instance and forces it to open in read only mode. I do this because the users aren't doing any edits and I don't want them saving the file.

It works good but I've noticed while it's opened and I open another workbook (double clicking in Explorer) it opens in the same instance as the original one I opened with the script.

Is there a way, inside the workbook, to prevent other workbooks from opening in this instance? I don't want the users to close my workbook and have it close what they opened and may not have saved.

TYIA.

 

[Jaafar Tribak]

This should make your workbook an Orphan workbook ie : It will ensure that your workbook is always open alone in a seperate excel instance . This should be true whether the workbook is opened via the User Interface or via Code.

Place this code in the ThisWorkBook Module of your Orphan Workbook :

Private WithEvents oAppEvents As Application
 
Private oWb As Workbook
 
Private Sub Workbook_Open()
 
    Dim oNewApp As New Application
 
    If Application.Workbooks.Count > 1 Then
        Me.ChangeFileAccess xlReadOnly
        oNewApp.Workbooks.Open Me.FullName
        oNewApp.Visible = True
        Me.Close False
    End If
 
    Set oAppEvents = Application
 
End Sub
 
Private Sub oAppEvents_NewWorkbook(ByVal Wb As Workbook)
 
    Dim oNewApp As New Application
 
    Wb.Close False
    oNewApp.Workbooks.Add
    oNewApp.Visible = True
 
End Sub
 
Private Sub oAppEvents_WorkbookOpen(ByVal Wb As Workbook)
 
    If Wb Is Me Then Exit Sub
    Set oWb = Wb
    oWb.ChangeFileAccess xlReadOnly
    Application.OnTime Now, Me.CodeName & ".CloseWB"
 
End Sub
 
Private Sub CloseWB()
 
    Dim oNewApp As New Application
 
    oNewApp.Workbooks.Open oWb.FullName
    oNewApp.Visible = True
    oWb.Close False
 
End Sub

Now everytime you try opening a workbook it will be launched in a seperate Instance.

If you have an instance of excel already open and you try opening the Orphan workbook , the latter will be launched in a seperate instance.

 

[xenou]

Is there a way, inside the workbook, to prevent other workbooks from opening in this instance? I don't want the users to close my workbook and have it close what they opened and may not have saved.

Normal Excel behavior is that if you open a second workbook you can save it if you want to on close. This isn't affected by the first workbook being read-only. Why two instances of Excel? I'd find that unpleasant (whenever I get a second instance of Excel, usually by mistake, the first thing I do is close it as I don't like working that way).

 

[Rekd]

Jaafar,

Thanks for the code. I will plug it in and let you know.

Normal Excel behavior is that if you open a second workbook you can save it if you want to on close. This isn't affected by the first workbook being read-only. Why two instances of Excel? I'd find that unpleasant (whenever I get a second instance of Excel, usually by mistake, the first thing I do is close it as I don't like working that way).

The reasoning is simple; I use the workbook only to store and calculate data. I don't want the buyer's to see it or even know it's there. When they're using excel on something of their own and open my file/database/program to get a cost, then close it, it will disable alerts then close.

(This is the important part...) If they have another workbook open in the same instance as mine and have made changes, it will also be closed without saving, or prompting.

I don't want them hopping over the cubicle walls with a hatchet ready to chop my thumbs off.

whenever I get a second instance of Excel, usually by mistake, the first thing I do is close it as I don't like working that way

Personally, I like having twindows. In fact, I've got a macro assigned to a button that will move the current spreadsheet to a new instance just so I can look at it by clicking elsewhere on my screen or put it on the other monitor and compare side-by-side. (MS's feeble attempt at a user-friendly MDI for Excel is lacking at best.)

 

[xenou]

The reasoning is simple; I use the workbook only to store and calculate data. I don't want the buyer's to see it or even know it's there. When they're using excel on something of their own and open my file/database/program to get a cost, then close it, it will disable alerts then close.

(This is the important part...) If they have another workbook open in the same instance as mine and have made changes, it will also be closed without saving, or prompting.

You haven't given a reason why your workbook closing without prompts makes other workbooks close without prompts - you shouldn't need to disable alerts, or use code that would close all open workbooks without prompts. I don't get why that is happening here.

 

[bradyboyy88]

This should make your workbook an Orphan workbook ie : It will ensure that your workbook is always open alone in a seperate excel instance . This should be true whether the workbook is opened via the User Interface or via Code.

Place this code in the ThisWorkBook Module of your Orphan Workbook :

Private WithEvents oAppEvents As Application
 
Private oWb As Workbook
 
Private Sub Workbook_Open()
 
    Dim oNewApp As New Application
 
    If Application.Workbooks.Count > 1 Then
        Me.ChangeFileAccess xlReadOnly
        oNewApp.Workbooks.Open Me.FullName
        oNewApp.Visible = True
        Me.Close False
    End If
 
    Set oAppEvents = Application
 
End Sub
 
Private Sub oAppEvents_NewWorkbook(ByVal Wb As Workbook)
 
    Dim oNewApp As New Application
 
    Wb.Close False
    oNewApp.Workbooks.Add
    oNewApp.Visible = True
 
End Sub
 
Private Sub oAppEvents_WorkbookOpen(ByVal Wb As Workbook)
 
    If Wb Is Me Then Exit Sub
    Set oWb = Wb
    oWb.ChangeFileAccess xlReadOnly
    Application.OnTime Now, Me.CodeName & ".CloseWB"
 
End Sub
 
Private Sub CloseWB()
 
    Dim oNewApp As New Application
 
    oNewApp.Workbooks.Open oWb.FullName
    oNewApp.Visible = True
    oWb.Close False
 
End Sub

Now everytime you try opening a workbook it will be launched in a seperate Instance.

If you have an instance of excel already open and you try opening the Orphan workbook , the latter will be launched in a seperate instance.

For some reason the code is not working for me. The first block of code works but the other ones which keep other documents from loading in is not working. Do you think its because the appevent needs to be placed in a class module?