Currently I have a sheet that uses a semaphore type code to keep constants
and classes in memory while the user uses the sheet. I have the code in the
workbook_open event, using a checkbox on a sheet to keep it going:

'Now keep system Idle so WBS never goes out of scope
While gcwbThis.Sheets("Control").chkRun.Value

DoEvents

'Now Check for any events
If ef.AddRow Or ef.DeleteRow Or ef.RefreshControl Or ef.ResetControl
Or _
ef.ControlActivate Or ef.ControlChange Or ef.WBSWorking Or _
ef.WBSCheckUncheck Then

'Turn off Screen updates
ScreenUpdating False

Control_Functions

WBS_Functions

ScreenUpdating True

End If

Wend

The only issue is that I cannot open another workbook via double-clicking or
activate an open workbook via clicking on it. I can open a workbook and
switch via the menu controls. Is there a flag or way that can tell me when
another sheet is attempting to open so I can allow it in, even when just
double-clicking on it?


--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

I assume you have decided that you really need code like this running in a
loop and there is no <better way.

When you open an xls from Explorer, DDE is used to inform Excel that a WB
wants to open. I don't use DDE, but maybe look into one of those methods.
However, given the nature of you code, it may be better to isolate your
Excel instance from DDE with:
Application.IgnoreremoteRequests=True

NickHK

"J Streger" wrote in message
...
Currently I have a sheet that uses a semaphore type code to keep constants
and classes in memory while the user uses the sheet. I have the code in
the
workbook_open event, using a checkbox on a sheet to keep it going:

'Now keep system Idle so WBS never goes out of scope
While gcwbThis.Sheets("Control").chkRun.Value

DoEvents

'Now Check for any events
If ef.AddRow Or ef.DeleteRow Or ef.RefreshControl Or ef.ResetControl
Or _
ef.ControlActivate Or ef.ControlChange Or ef.WBSWorking Or _
ef.WBSCheckUncheck Then

'Turn off Screen updates
ScreenUpdating False

Control_Functions

WBS_Functions

ScreenUpdating True

End If

Wend

The only issue is that I cannot open another workbook via double-clicking
or
activate an open workbook via clicking on it. I can open a workbook and
switch via the menu controls. Is there a flag or way that can tell me when
another sheet is attempting to open so I can allow it in, even when just
double-clicking on it?


--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

Thanks Nick. Seems to work just fine.

And the only reason is performance. Reloading the class objects each time is
time consuming and of course I'm looking for as little lag time as possible.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003



"NickHK" wrote:

I assume you have decided that you really need code like this running in a
loop and there is no <better way.

When you open an xls from Explorer, DDE is used to inform Excel that a WB
wants to open. I don't use DDE, but maybe look into one of those methods.
However, given the nature of you code, it may be better to isolate your
Excel instance from DDE with:
Application.IgnoreremoteRequests=True

NickHK

"J Streger" wrote in message
...
Currently I have a sheet that uses a semaphore type code to keep constants
and classes in memory while the user uses the sheet. I have the code in
the
workbook_open event, using a checkbox on a sheet to keep it going:

'Now keep system Idle so WBS never goes out of scope
While gcwbThis.Sheets("Control").chkRun.Value

DoEvents

'Now Check for any events
If ef.AddRow Or ef.DeleteRow Or ef.RefreshControl Or ef.ResetControl
Or _
ef.ControlActivate Or ef.ControlChange Or ef.WBSWorking Or _
ef.WBSCheckUncheck Then

'Turn off Screen updates
ScreenUpdating False

Control_Functions

WBS_Functions

ScreenUpdating True

End If

Wend

The only issue is that I cannot open another workbook via double-clicking
or
activate an open workbook via clicking on it. I can open a workbook and
switch via the menu controls. Is there a flag or way that can tell me when
another sheet is attempting to open so I can allow it in, even when just
double-clicking on it?


--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003