Stop the oening of another workbook in a certain instance.

[bandit_1981]

Hello all.

I am trying to keep the users from being able to have more then just my WB open in that specific instance of excel. Right now they use a small exe to load up the spread sheet and i always create a new instance of excel so there is not an issue. The issue occurs when they open my WB then instead of opening excel and putting another WB in a seperate instance they just double click it so now i have 2 WB in the same instance. This messess up my work and sometimes causes a hard Crash. Is there a way to prevent them from opening another WB in that instance?

Thanks

 

[helloworld]

I am not an expert, but this seems to work:

In the workbook that you want to isolate, create a Class Module and paste this code (let's say you name it AppWithEvents):

Public WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    If (App.Workbooks.Count > 1) Then
        MsgBox "You cannot create other workbooks in this instance!"
        Wb.Close False
    End If
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    If (App.Workbooks.Count > 1) Then
        MsgBox "You cannot open other workbooks in this instance!"
        Wb.Close False
    End If
End Sub

Then in the "ThisWorkbook" object of the same workbook do this:

Dim gApp As New AppWithEvents

Private Sub Workbook_Open()
    Set gApp.App = Application
End Sub

Whenever you try to open another workbook, this code will send a message to the user and close it (I don't know how to stop the open, but hopefully the effect is the same). The same will happen if you try to create a new workbook. Even when you try double clicking another Excel file (*.xls), and Windows tries to open the file with the Excel instance in which the isolated workbook is running, the open will be rejected. As usual, this won't work if the user disables the macros, but then your own macros wouldn't work either!

I hope this helps!

HW.