Allow only 1 Excel File to Open

[jgspencer]

Is it possible to write code that can prevent any other excel file to open while the current file is open? The reason I ask is because I have a schedule template with all command bars disabled, but when they open another file, some of the command bars become visible. Thanks for your help.

 

[xenou]

Perhaps you could put your code to disable the command bars in a workbook_activate event so that no matter what when your schedule is being used/activated these command bars are disabled.

As far as not allowing another workbook to be opened, you could try playing with the workbook_deactivate event...perhaps activating the workbook again! Sounds annoying to me but there you go.

Beware that code can sometimes be evaded just by disabling macros.

Let us know how you are doing as there sure to be other ideas.

Alex

 

[jgspencer]

Well, actually I had the disabling and enabling of the command bars in the workbook_activate and workbook_deactivate. This was my problem because when a new workbook opened in the same instance it activated and deactivated the first instance causing the command bars to be enabled. So I moved them to a workbook_open and workbook_close event to solve the first issue.

Then I added the code:


----------------------------------------------------
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)

If ActiveWorkbook.Name <> ThisWorkbook.Name Then

Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.ScreenUpdating = True
Sheets("SUM").Select
Range("A1").Select

MsgBox "The Scheduler can be the ONLY Excel File open in this instance!!! Please close this file before you open another Excel file.", vbOKOnly, "Schedule Workbook"
End If

With ActiveWorkbook
Application.ScreenUpdating = False
Application.CommandBars("Full Screen").Enabled = False
.Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.CommandBars("Cell").Enabled = False
Application.CommandBars("Ply").Enabled = False
Application.CommandBars("Standard").Enabled = False
'Application.DisplayFormulaBar = False
Application.DisplayAlerts = False
Call ToggleCutCopyAndPaste(False)
Dim wsSheet As Worksheet
Dim sSheetStart

Set sSheetStart = ActiveSheet
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each wsSheet In Worksheets
wsSheet.Activate
ActiveWindow.DisplayHeadings = False
Next

sSheetStart.Activate
Application.ScreenUpdating = True
Application.EnableEvents = True

End With
End Sub
-----------------------------------------------------------------

I got help with this code from another forum. This prevents any other workbook from opening. Since the disabling of the command bars is no longer on workbook_activate, they remain the same. Problem solved.

As far as, evading the code by disabling macros, I have a solution for this. I use a macro enabler that makes users enable macros to use the template. if they do not, then all they will be able to access is the prompt screen. All other sheets are very hidden and will not show until macros are disabled thus protecting my sheet and preventing access without enabling macros. If they choose not to enable macros, only the prompt sheet can be viewed and there is no ability to unhide the sheets. I then password protect my code preventing access to change this as well. There is probably away around this by experienced vba coders, but my employees at the restaurants will not have the knowledge to get past it, thus ensuring the macros will be enabled and my code to work as planned.

I got the macro enabler code from another website and tweaked it a little to my needs. Let me know if you would like me to post it, but I didn't write it, just found it.