Hi All
I have an application (my-application) that replaces Excel standard toolbars
with a custom toolbar, the toolbars are restored if the user closes the
my-application.

If however my-application is running and the user chooses to create or open
another workbook, I need to ensure that the custom toolbar is removed and
the standard toolbars restored, if they switch back to the my-application
then the reverse action is required, the procedure that turns of the
standard tool bars and creates the customer toolbar runs again.

I have been trying to use windows and workbook activate and workbook
deactivate events without much success, it appears the focus on
my-application is lost immediately these events are triggered - what is the
optimum strategy to achieve this switching?

TIA

--
Cheers
Nigel

Nigel,

This code, adapted from a previous post, seems to work fine for me


Private VisibleState()

Private Sub Workbook_Activate()
Dim a As Long
Dim cCBs As Long
Application.CommandBars("Worksheet Menu Bar").Enabled = False
ReDim VisibleState(1)
cCBs = 1
For a = 2 To Application.CommandBars.Count
If Application.CommandBars(a).Visible = True Then
ReDim Preserve VisibleState(cCBs)
VisibleState(cCBs) = Application.CommandBars(a).Name
Application.CommandBars(a).Visible = False
cCBs = cCBs + 1
End If
Next a
End Sub

Sub Workbook_Deactivate()
Dim a As Long
Application.CommandBars("Worksheet Menu Bar").Enabled = True
For a = LBound(VisibleState) To UBound(VisibleState)
Application.CommandBars(VisibleState(a)).Visible = True
Next a
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nigel" wrote in message
...
Hi All
I have an application (my-application) that replaces Excel standard
toolbars
with a custom toolbar, the toolbars are restored if the user closes the
my-application.

If however my-application is running and the user chooses to create or
open
another workbook, I need to ensure that the custom toolbar is removed and
the standard toolbars restored, if they switch back to the my-application
then the reverse action is required, the procedure that turns of the
standard tool bars and creates the customer toolbar runs again.

I have been trying to use windows and workbook activate and workbook
deactivate events without much success, it appears the focus on
my-application is lost immediately these events are triggered - what is
the
optimum strategy to achieve this switching?

TIA

--
Cheers
Nigel

You are asking the right questions, which is more than many who
implement their own 'dictator' systems do. {grin}

Take the problem you've discovered a step further. Suppose your addin
crashes. Or XL crashes. Or the computer loses power. How does the
user regain use of XL?

The optimum strategy to manage switching between your environment and
the XL environment is "Don't do it."

If you are writing a program that doesn't need XL's capabilities, don't
use XL. Write a standalone program.

If your addin requires XL, coexist. Don't fight XL. Leverage it.

If your data source is in XL and you must 'disable' XL-centric access
to it, consider either of two options: open the file and hide the
window; or instantiate another copy of XL and open the file in that
(invisible by default) copy of XL.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , nigel-
says...
Hi All
I have an application (my-application) that replaces Excel standard toolbars
with a custom toolbar, the toolbars are restored if the user closes the
my-application.

If however my-application is running and the user chooses to create or open
another workbook, I need to ensure that the custom toolbar is removed and
the standard toolbars restored, if they switch back to the my-application
then the reverse action is required, the procedure that turns of the
standard tool bars and creates the customer toolbar runs again.

I have been trying to use windows and workbook activate and workbook
deactivate events without much success, it appears the focus on
my-application is lost immediately these events are triggered - what is the
optimum strategy to achieve this switching?

TIA

Tushar,
Thanks for the advice, in fact I had come to a similar conclusion. My
application is now stand-alone, in which the menu and command bar system is
totally customised. I made the decision that users are prevented from
creating new workbooks (or sheets) but do allow them to copy selectively
into another target application - could be another instance of Excel.

I still have a potential problem(?) with the CommandBars collection - I
actually disable all standard xl menus, command bars and popups.
Re-enabliing these on the way out - is this status stored in the XLB? If it
is then a power loss or other system crash will result in a user interface
corruption.

--
Cheers
Nigel



"Tushar Mehta" wrote in message
om...
You are asking the right questions, which is more than many who
implement their own 'dictator' systems do. {grin}

Take the problem you've discovered a step further. Suppose your addin
crashes. Or XL crashes. Or the computer loses power. How does the
user regain use of XL?

The optimum strategy to manage switching between your environment and
the XL environment is "Don't do it."

If you are writing a program that doesn't need XL's capabilities, don't
use XL. Write a standalone program.

If your addin requires XL, coexist. Don't fight XL. Leverage it.

If your data source is in XL and you must 'disable' XL-centric access
to it, consider either of two options: open the file and hide the
window; or instantiate another copy of XL and open the file in that
(invisible by default) copy of XL.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , nigel-
says...
Hi All
I have an application (my-application) that replaces Excel standard
toolbars
with a custom toolbar, the toolbars are restored if the user closes the
my-application.

If however my-application is running and the user chooses to create or
open
another workbook, I need to ensure that the custom toolbar is removed
and
the standard toolbars restored, if they switch back to the
my-application
then the reverse action is required, the procedure that turns of the
standard tool bars and creates the customer toolbar runs again.

I have been trying to use windows and workbook activate and workbook
deactivate events without much success, it appears the focus on
my-application is lost immediately these events are triggered - what is
the
optimum strategy to achieve this switching?

TIA

"Nigel" wrote in message
...
I still have a potential problem(?) with the CommandBars collection - I
actually disable all standard xl menus, command bars and popups.
Re-enabliing these on the way out - is this status stored in the XLB? If
it
is then a power loss or other system crash will result in a user interface
corruption.

You could save the details to a text file, and the next time the workbook is
opened, check if the text file exists, if so, open, read, and restore the
commandbars.