I have several spreadsheets which run macros, some of which conflict with
each other. I was wondering if there was some code I could add to existing
macros to block a user from opening a second spreadsheet while one of these
is opened. Any help appreciated.

McBarker,

Try these - the first macro disables the Open tool, the Open command from
the File menu and the Control+0 keyboard shortcut. the second macro
re-enables everything.

Regards

Pete

Sub OpenDisable()
Dim oCtrl As Office.CommandBarControl
For Each oCtrl In Application.CommandBars.FindControls(ID:=23)
oCtrl.Enabled = False
Next oCtrl
Application.OnKey "^{o}", "" 'disable Open keyboard shortcut
End Sub

Sub OpenEnable()
Dim oCtrl As Office.CommandBarControl
For Each oCtrl In Application.CommandBars.FindControls(ID:=23)
oCtrl.Enabled = True
Next oCtrl
Application.OnKey "^{o}" 'enable Open keyboard shortcut
End Sub


"McBarker" wrote:

I have several spreadsheets which run macros, some of which conflict with
each other. I was wondering if there was some code I could add to existing
macros to block a user from opening a second spreadsheet while one of these
is opened. Any help appreciated.

McBarker,
You can either put some code in the Workbook_Open event, to check which WBs
are already open and advise the user.
Or use Application level events so Excel will trigger and you can check
whether the open should be allowed or not.
http://www.cpearson.com/excel/AppEvent.htm

NickHK

"McBarker" wrote in message
...
I have several spreadsheets which run macros, some of which conflict with
each other. I was wondering if there was some code I could add to existing
macros to block a user from opening a second spreadsheet while one of
these
is opened. Any help appreciated.

Thanks guys. I'll try the code below next week, and see if it works out. One
of the problems is that the spreadsheets in question are opened from a
desktop shortcut, which would bypass the Excel menu and toolbar commands.
Something along the lines of the following pseudocode would make what I'm
looking for easier:

[1] Spreadsheet1 is in use
[2] User opens Spreadsheet2 (from desktop shortcut)
[3] Code in the Spreadsheet2 Auto_Open() macro detects that Spreadsheet1 is
already open and in use
[4] A warning message is displayed
[5] Spreadsheet2 automatically closes

I'm just not sure of how to code line [3].

Regards
McBarker

Peter Rooney wrote:
McBarker,

Try these - the first macro disables the Open tool, the Open command
from the File menu and the Control+0 keyboard shortcut. the second
macro re-enables everything.

Regards

Pete

Sub OpenDisable()
Dim oCtrl As Office.CommandBarControl
For Each oCtrl In Application.CommandBars.FindControls(ID:=23)
oCtrl.Enabled = False
Next oCtrl
Application.OnKey "^{o}", "" 'disable Open keyboard shortcut
End Sub

Sub OpenEnable()
Dim oCtrl As Office.CommandBarControl
For Each oCtrl In Application.CommandBars.FindControls(ID:=23)
oCtrl.Enabled = True
Next oCtrl
Application.OnKey "^{o}" 'enable Open keyboard shortcut
End Sub


"McBarker" wrote:

I have several spreadsheets which run macros, some of which conflict
with each other. I was wondering if there was some code I could add
to existing macros to block a user from opening a second spreadsheet
while one of these is opened. Any help appreciated.

I need help with line 3 of the following:

[1] Spreadsheet1 is already in use
[2] User opens Spreadsheet2 (from desktop shortcut - bypassing Excel menus)
[3] Code in the Spreadsheet2 Auto_Open() macro detects that Spreadsheet1 is
already open and in use
[4] A warning message is displayed
[5] Spreadsheet2 automatically closes

Regards
McBarker