Showing VBA User Forms as Task Panes

While CTPs are a very useful addition to Excel VBA, you're either limited by the set of generally available ActiveX controls or you need to learn how to create your own ActiveX controls. Although this is beyond the scope of this book, it is actually quite easy to do, using either VB6 or .NET.

There is, however, a third possibility, which is for someone to create a custom ActiveX control that can in turn host a normal VBA UserForm — and we've done exactly that! The OACTPUserformHost is a single ocx file containing both a COM Add-In and an ActiveX control, and it's available as one of the download files for this chapter from

Load the COM Add-In by using the Add... button on the COM Add-Ins dialog (Office Menu O Excel Options O Add-Ins O Manage: COM Add-Ins O Go), choosing Files of Type: All Files, and navigating to the OACTPUserformHost.ocx file.

You can show a standard VBA UserForm as a task pane by including the following code within the UserForm's code module. You'll first need to add a Project reference to the OACTPUserformHost library, listed as Custom Task Pane Userform Host in the Project References dialog. If not in the list, it can be added by clicking the Browse button, choosing Files of Type: ActiveX Controls, and navigating to the OACTPUserformHost.ocx file.

'A WithEvents object variable to refer to the Custom Task Pane Userform Host 'object. Requires a reference to OACTPUserformHost.OCX, listed as '"Custom Task Pane Userform Host" in the Project References dialog Dim WithEvents moCTP As CTPUserformHost

' Public method to show a VBA userform in a custom ' task pane. Typical usage is:

' UserformName.ShowAsTaskPane

Public Sub ShowAsTaskPane()

If moCTP Is Nothing Then

'Create a task pane with the required title

Set moCTP = Application.COMAddIns("OACTPUserformHost.Connect").Object _ .CreateUserformTaskPaneHost(Me, "Hello World")

With moCTP

'Set the task pane's properties

'(default = msoCTPDockPositionRestrictNone) .DockPositionRestrict = msoCTPDockPositionRestrictNone

'(default = msoCTPDockPositionRight) .DockPosition = msoCTPDockPositionRight

'Tell the task pane whether to handle the userform's resizing '(default = False) .HandleResizing = True End With End If

'Make the task pane visible moCTP.Visible = True

End Sub

'Close the CTP when the form is unloaded Private Sub UserForm_Terminate() On Error Resume Next moCTP.Visible = False Set moCTP = Nothing End Sub

A call to Userform.ShowAsTaskPane uses the OACTPUserformHost COM Add-In to create a custom task pane hosting the UserForm, which is then initialized and displayed. As well as acting as a host for the UserForm, the COM Add-In also handles the form's resizing, using the technique for resizable forms shown in Chapter 27.

+4 -6

Post a comment