Allow only 1 Excel File to Open

jgspencer

New Member
Joined
Apr 3, 2009
Messages
35
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
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

New Member
Joined
Apr 3, 2009
Messages
35
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.
 

Forum statistics

Threads
1,082,139
Messages
5,363,363
Members
400,731
Latest member
Jackserver

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top