Workbook Switching Strategy

[Nigel]

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

 

[Bob Phillips]

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)

 

[Tushar Mehta]

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

 

[Nigel]

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.

 

[Bob Phillips]

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.

 

[Nigel]

Bob, thanks for the tip. The problem is not with my application, that
restores itself, the issue is with opening XL 'normally' so unless the I
store some code that will always run I am a bit snookered! Suppose it code
be in the XL start up directory, but the macro security might get in the
way? I had thought of using the registry but I think that might be even
more difficult?

 

[Bob Phillips]

Nigel,

It would be simple and you could put the code in Personal.xls or an add-in,
so that it fires and checks the status when Excel opens normally.

Want me to knock you up a simple add-in that does it (you do know about
installing add-ins I presume)?

--

HTH

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

 

[Nigel]

That would be great - thanks very much