Prevent multiple workbooks in same Excel instance
 

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

Prevent multiple workbooks in same Excel instance
 

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


Gord Dibben MS Excel MVP

On Thu, 17 Sep 2009 01:10:17 -0700 (PDT), Leon
wrote:

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

Prevent multiple workbooks in same Excel instance
 

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?



On 17 Sep., 17:49, Gord Dibben <gorddibbATshawDOTca wrote:
What happens to the ontime code when you open another workbook?

Gord Dibben *MS Excel MVP

On Thu, 17 Sep 2009 01:10:17 -0700 (PDT), Leon
wrote:



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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Prevent multiple workbooks in same Excel instance
 

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

On Thu, 17 Sep 2009 12:54:35 -0700 (PDT), Leon
wrote:

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?



On 17 Sep., 17:49, Gord Dibben <gorddibbATshawDOTca wrote:
What happens to the ontime code when you open another workbook?

Gord Dibben *MS Excel MVP

On Thu, 17 Sep 2009 01:10:17 -0700 (PDT), Leon
wrote:



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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Prevent multiple workbooks in same Excel instance
 

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

On Thu, 17 Sep 2009 14:39:54 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Thu, 17 Sep 2009 12:54:35 -0700 (PDT), Leon
wrote:

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?



On 17 Sep., 17:49, Gord Dibben <gorddibbATshawDOTca wrote:
What happens to the ontime code when you open another workbook?

Gord Dibben *MS Excel MVP

On Thu, 17 Sep 2009 01:10:17 -0700 (PDT), Leon
wrote:



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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Prevent multiple workbooks in same Excel instance
 

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

On 18 Sep., 00:09, Gord Dibben <gorddibbATshawDOTca wrote:
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



On Thu, 17 Sep 2009 14:39:54 -0700, Gord Dibben <gorddibbATshawDOTca wrote:
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

On Thu, 17 Sep 2009 12:54:35 -0700 (PDT), Leon
wrote:

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?

On 17 Sep., 17:49, Gord Dibben <gorddibbATshawDOTca wrote:
What happens to the ontime code when you open another workbook?

Gord Dibben *MS Excel MVP

On Thu, 17 Sep 2009 01:10:17 -0700 (PDT), Leon
wrote:

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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Prevent multiple workbooks in same Excel instance
 

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 wrote:

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

--

Dave Peterson

Prevent multiple workbooks in same Excel instance
 

Thanks for jumping in Dave.

Gord

On Fri, 18 Sep 2009 07:13:18 -0500, Dave Peterson
wrote:

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 wrote:

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

Prevent multiple workbooks in same Excel instance
 

Hi Dave

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

Regards


On 18 Sep., 18:06, Gord Dibben <gorddibbATshawDOTca wrote:
Thanks for jumping in Dave.

Gord

On Fri, 18 Sep 2009 07:13:18 -0500, Dave Peterson



wrote:
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 wrote:

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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -