Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Switching Strategy
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Switching Strategy
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Switching Strategy
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Switching Strategy
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Switching Strategy
"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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Switching Strategy
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" wrote in message ... 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? -- Cheers Nigel "Bob Phillips" wrote in message ... "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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Switching Strategy
That would be great - thanks very much
-- Cheers Nigel "Bob Phillips" wrote in message ... 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" wrote in message ... 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? -- Cheers Nigel "Bob Phillips" wrote in message ... "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Switching between sheets in same workbook (Like Alt + Tab & Choose | Excel Discussion (Misc queries) | |||
Is there a shortcut key for switching between tabs in a workbook | Excel Discussion (Misc queries) | |||
short cut key for switching between sheets in a workbook? | Excel Discussion (Misc queries) | |||
Strategy Needed | Excel Discussion (Misc queries) | |||
Strategy Query | Excel Programming |