問題描述
Excel 2007 - 捕獲打開命令欄按鈕事件 (Excel 2007 - Catch open command bar button event)
Prior to Excel 2007, we used to catch the open file event in Excel using vba so that we could show our own open dialog that knew how to find client folders. In Excel 2007 this no longer works, does anyone have any ideas on how it works now>
The basic premise before was create a class with a commandbar button and a related event and then set that commandBarButton when the app opened and it would catch it, but now it does not. Is there another way now?
Public WithEvents cmdBold As Office.CommandBarButton Private Sub cmdBold_Click (ByVal Ctrl As Office.CommandBarButton, _ CancelDefault As Boolean) ' Insert code you want to run in response to this event. End Sub
http://msdn.microsoft.com/en-us/library/aa189726(office.10).aspx
參考解法
方法 1:
Not really up to speed with 2007 yet but I think that the change to the Ribbon UI has changed the way your application works.
From the "Developer Considerations" section of this lengthy MSDN article:
Compatibility of Legacy Solutions and Customizations
Many companies rely on custom applications built on the Microsoft Office system and solutions that integrate Microsoft Office with third-party applications. Many of these solutions modify the Office UI through the Command Bars object model. In the 2007 Office release, this code continues to work—in most cases without modification.
Changes made to toolbars in Office 2003 typically appear in the 2007 release on an Add-Ins tab in the Ribbon (see Figure 18). The type of customization that appears depends on the original design of the add-in. For example, if a customization added items to the previous menu structure, the Office Fluent UI creates a Menu Commands group. If the custom code added items to the built-in toolbars, the Office Fluent UI creates a Toolbar Commands group that contains these controls. Toolbars added by an add-in show up in the Custom Toolbars group.
Mouse clicks and other events on the Add-Ins tab trigger the same results as in the previous menus and toolbar interface. Add-in code that executes when a toolbar button is clicked in the previous UI still executes when the user clicks the corresponding button in the Toolbars group of the Add-Ins tab in the 2007 release.
Requests to hide menus and toolbars or to remove controls are ignored. If an add-in attempts to repurpose built-in functionality by changing the behavior of menu items or toolbar buttons, such as Save or Print Preview, the Office application copies the control to the Add-Ins tab and changes it there, leaving the original control on the Ribbon or Microsoft Office Button menu unchanged.
Although it is possible to remove or disable built-in controls programmatically in the 2007 release, add-ins that remove or reorder menu items and toolbar items in previous releases of Microsoft Office have no effect when they are loaded.
So, you may well find that you have an additional "Open" control in the Add-Ins tab.
It seems that re-purposing the built-in control is only possible using XML. From earlier in the same document:
Opportunities for Extensibility
Repurposing Built-in Controls. You can take over the function of any built-in control anywhere in the UI by writing a single line of XML that connects the custom code to every instance of the built-in control. You can modify the control to execute custom code and, optionally, proceed with the built-in functionality.
Finally, this even longer MSDN article explains how XML customization works