Prevent other Excel Workbooks from Opening

[jgspencer]

Is there any way to prevent all other excel workbooks from opening when I have the current workbook open. The reason why I ask is because I have a scheduling template that disables or hides most command bars to prevent the users from making any changes, but when another excel workbook is opened, it enables most of the command bars or makes them visible. I have found a few things on the internet but could not get any of them to work.

Any help would be greatly appreciated.

 

[SkipVought]

Hi,

I have found a few things on the internet but could not get any of them to work.

What did you try and what were the results.

Please be clear, concise and complete.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jgspencer]

Here are the two websites and code that I could find, the websites, the code used and the results of each.


1.I tried the code from this website first:

http://www.xtremevbtalk.com/showthread.php?t=93031

--------------------------------
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
'This checks to make sure the user is opening one of my forms and not another Excel document
If Sheet1.lblMainMenu.Caption <> "Main" Then
MsgBox "If you need to open another Excel Workbook, please click on Start/Programs/Microsoft Office/Microsoft Excel" & vbCrLf & _
"After Excel opens then click on File/Open and then try to open your file.", vbInformation, "PHIS Request Forms"
'This closes out of the form they tried to open and reactivates my form
ActiveWorkbook.Close
Windows("Access Request Form (New User).xls").Activate
End If
End Sub
---------------------------------
When I placed this code into ThisWorkbook, I get the following error message:

"Compile Error:

Method or Data Member not found"

It errors at ".lblMainMenu" and other workbooks were still able to open in the same instance of excel.


2. The second code I tried came from the following website:

http://www.vbforums.com/archive/index.php/t-238006.html

with the following code:

------------------------------------

Put this in a class module called cApp:

Public WithEvents CApp As Application

Private Sub CApp_WorkbookOpen(ByVal wb As Excel.Workbook)

' Capture opened workbook info
Dim sPathName As String
sPathName = wb.Path & "\" & wb.Name

' Close it and open it in a new instance
wb.Close
Dim NewExcel As Excel.Application
Set NewExcel = CreateObject("Excel.Application")
With NewExcel
.Visible = True
.Workbooks.Open (sPathName)
End With

End Sub


Put this in a module:

Option Explicit

Public ClassApp As New CApp

Public Sub YourSub()

' Initialize and instantiate your class object to access application events.
Set ClassApp.CApp = Excel.Application

End Sub
-----------------------------------

While this did not create an error, it did not work as other workbooks were still able to open in the same instance of excel instead of opening in a new instance.


Hopefully I have included enough information and hopefully, there is a way to achieve this. Let me know if you need any further details.

 

[SkipVought]

Try pasting this in the ThisWorkbook Object Code Window...

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    If ActiveWorkbook.Name <> ThisWorkbook.Name Then
        ActiveWorkbook.Close
    End If
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Use the above in conjunction with the second method, which worked for you, to prevent another instance of Excel from opening.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jgspencer]

Thanks SkipVought for your help, that worked great.