When task panes were introduced in Office XP, developers were soon eager to use them for their own content. In Office 2007, Microsoft has answered that request by adding the ability for COM Add-Ins to create custom task panes (CTPs), using custom ActiveX controls to define their content. Just like RibbonX, the COM Add-In tells Excel that it contains a custom task pane by implementing another interface, ICustomTaskPaneConsumer. A COM Add-In, therefore, needs to do the following to create a CTP:
□ Implement the ICustomTaskPaneConsumer interface, which contains a single method, CTPFactoryAvailable.
□ The CTPFactoryAvailable procedure is passed a reference to Excel's CTP factory class, which the Add-In stores in a module-level variable.
□ Design a custom ActiveX control to provide the content of the CTP.
□ Create, show, and hide the CTP in response to a user trigger.
From a VBA developer's perspective, the restriction that you can only use ActiveX controls to provide the CTP content is a rather nasty one, because you can't create ActiveX controls using VBA. There are, however, two ways to get around that. The first is to realize that there are a number of readily available ActiveX controls that you can drop into a CTP, such as the Web Browser control. All you need is a simple COM Add-In that exposes the capability to create new CTPs to VBA, then use VBA code to create a CTP containing a Web Browser control and automate the Web Browser control to show an HTML page. To create the COM Add-In, follow the steps listed in the "Adding Ribbon Controls" section, give the project the name OACTPVBA, and copy in the following code to implement the ICustomTaskPaneConsumer interface and expose CTP creation to VBA:
'Tell Excel that we're working with custom task panes Implements ICustomTaskPaneConsumer
'Store a reference to Excel's CTP factory class Dim moCTPFactory As ICTPFactory
'Expose the functions in this class to VBA
Private Sub AddInInstance_OnConnection(ByVal Application As Object, _
ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _ ByVal AddInInst As Object, custom() As Variant) AddInInst.object = Me End Sub
'Called by Excel when the Add-In is loaded, 'providing a factory object that we use to 'create custom task panes.
Private Sub ICustomTaskPaneConsumer_CTPFactoryAvailable( _ ByVal CTPFactoryInst As Office.ICTPFactory) 'Store a reference to the factory object, for use when required Set moCTPFactory = CTPFactoryInst End Sub
'Expose CTP-creation to VBA, e.g. to create a web browser task pane: 'Dim moCTP As CustomTaskPane
'Set moCTP = Application.ComAddIns("OACTPVBA.Connect").Object
' .CreateTaskPane("Internet Explorer","Shell.Explorer.2")
Public Function CreateTaskPane(ByVal sTitle As String, _
ByVal sProgID As String) As Office.CustomTaskPane
On Error Resume Next
Set CreateTaskPane = moCTPFactory.CreateCTP(sProgID, sTitle) End Function
Compile the DLL, start Excel, and use the following VBA code in a standard module to show Google in a custom task pane:
'Keep a reference to the CTP, so we can show/hide it Dim moCTP As CustomTaskPane
'Create the CTP containing a Web Browser control
Set moCTP = Application.COMAddIns("OACTPVBA.Connect").Object _
.CreateTaskPane("Internet Explorer", "Shell.Explorer.2")
'Show the CTP moCTP.Visible = True
'Navigate to Google moCTP.ContentControl.navigate "http://www.google.com" End Sub
It's enlightening to compare the way in which CTPs have been implemented to the RibbonX mechanism. For CTPs, when the COM Add-In starts up, Excel passes a factory class into the CTPFactoryAvailable method, which is stored in a module-level variable. You can then use the factory class to create new instances of custom task panes at any time you like. In turn, that allows you to very easily expose the new CTP functionality to VBA. In contrast, the RibbonX design requires you to specify all customization as soon as the COM Add-In starts, so you can only create one customization snippet and can neither expose the RibbonX features to VBA nor create dynamic interfaces that respond to the changing Excel environment.
Was this article helpful?
Get Inspired By The Most Popular Bloggers Online! If You Want To Skyrocket Your Success With Business And Improve Your Overall Life You Need To Have A Look At The Accidental Blogging Millionaires! Business can be a fight, particularly when you’re trying to establish one online and like all fights, to succeed you must find the winning techniques and apply them.