Prevent multiple workbooks in same Excel instance

[Leon]

Hi

I have a workboook which is updated every minute by using the ontime
facility.
It works fine when user not have any other workbook open in the same
intance of Excel.

How do I prevent user from open another workbook in the particular
instance containing the workbook with autoupdate function (ontime)

1) when user tries to open another workbook via the open menu
2) when user dobbleclick on an xls file

Regards

 

[Gord Dibben]

What happens to the ontime code when you open another workbook?


Gord Dibben MS Excel MVP

 

[Leon]

The ontime loops and starts same sub every minute.
The code shifts sheets - managing data - updates graphs - and stuff.
Finally set focus at the sheet that was active when the ontime
started.

I have tried to put "thisworkbook" all places needed - but maybe I
missed something.

I did succeeded making the code so that it do not break with an End/
debug question, but the code bring the workbook1 in front when the
code stops and places focus at the sheet that was active in the
workbook1 at the time the ontime started.

Is it possible to make ontime fool around in workbook1 and at the same
time a user can work with another workbook2 not being disturbed by tha
workbook1 macro that starts every minute?

 

[Gord Dibben]

Macros can't run in the background while you work on another workbook.

The ontime macro will disturb whatever you're doing every time it
runs...........every minute.

I am not skilled enough in VBA to make any suggestions about keeping people
from opening another workbook while the ontime macro workbook is open.


Gord

 

[Gord Dibben]

Have you thought about running your ontime macro workbook in one instance of
Excel and the users play around with other workbooks in another instance of
Excel.

The two won't communicate so won't disturb each other.


Gord

 

[Leon]

That was excatly what my question was about.. I'm not explicit enough
though?

How do I prevent users open workbook in the running instance in the
mentioned situations..

1) when the user doubleclick on an Excel file in windows explorer
2) when user opens another workbook from menu - can it be redirected
to a new instance of Excel?

cheers

 

[Dave Peterson]

I'm not sure what you're doing...

But one way you could prevent the user from using that instance of excel is to
open it via code and hide the application.

If the application has to be visible, you could use an application event that
counts the number of existing workbooks are open and won't let the user open
another one.

You can read more about application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.aspx

All this would go into the ThisWorkbook module of your workbook.

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
If xlApp.Workbooks.Count > 1 Then
MsgBox "This workbook cannot be opened if other " _
& "workbooks are already opened"
Me.Close savechanges:=False
End If
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
If xlApp.Workbooks.Count > 1 Then
Wb.Close savechanges:=False
MsgBox "Can't create a new workbook now!"
End If
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
If xlApp.Workbooks.Count > 1 Then
Wb.Close savechanges:=False
MsgBox "Can't open a new workbook now!"
End If
End Sub

These events will only work if the user has enabled macros and has not disabled
events.

 

[Gord Dibben]

Thanks for jumping in Dave.

Gord

I'm not sure what you're doing...

But one way you could prevent the user from using that instance of excel is to
open it via code and hide the application.

If the application has to be visible, you could use an application event that
counts the number of existing workbooks are open and won't let the user open
another one.

You can read more about application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.aspx

All this would go into the ThisWorkbook module of your workbook.

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
If xlApp.Workbooks.Count > 1 Then
MsgBox "This workbook cannot be opened if other " _
& "workbooks are already opened"
Me.Close savechanges:=False
End If
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
If xlApp.Workbooks.Count > 1 Then
Wb.Close savechanges:=False
MsgBox "Can't create a new workbook now!"
End If
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
If xlApp.Workbooks.Count > 1 Then
Wb.Close savechanges:=False
MsgBox "Can't open a new workbook now!"
End If
End Sub

These events will only work if the user has enabled macros and has not disabled
events.

 

[Leon]

Hi Dave

Thanks - looks what I'm looking for! I'l try this asap.

Regards