Logo
HOWTO: Create a toolwindow for the VBA editor of Office from an add-in with Visual Studio .NET.

Author: Carlos J. Quintero (Microsoft MVP) Applies to: Microsoft Office 64-bit
Date: September 2012   Microsoft Office 32-bit
       
Introduction

This article explains how to create a toolwindow (dockable window like the Project Explorer) from an add-in for the VBA editor of Office using Visual Studio .NET and the .NET Framework.

The sample code creates two buttons on the Standard toolbar that show a different toolwindow each one.

More information

Toolwindows are dockable, modeless windows that are created calling the VBE.Windows.CreateToolWindow method. This method returns a VBE.Window instance, which can be made visible setting its Window.Visible property to True. When closed, toolwindows are not destroyed. Rather, they are made invisible.

Toolwindows host ActiveX UserDocuments inside, but UserDocuments don't exist in .NET. Fortunately, Usercontrols can be used instead, with one caveat: the toolwindow doesn't resize a UserControl automatically as it does with a UserDocument when creating add-ins with Visual Basic 6.0 (the sample code provides a workaround).

The VBE.Windows.CreateToolWindow method has these parameters:

  • (Input) The add-in instance (that was received in the OnConnection method).
  • (Input) The ProgId of the ActiveX UserDocument (or UserControl) that will be hosted. This class must be Public and visible to COM (so it needs the ComVisible attribute and set to True), and must be registered on the machine.
  • (Input) The caption of the toolwindow.
  • (Input) A GUID to uniquely identify the toolWindow. The VBA editor uses this GUID to store information specific to each toolwindow, such as its size or position.
  • (Output )The instance of the usercontrol that the method has created from the ProgId that you passed as second parameter.

To create the sample use the following steps:

  • Create an add-in for the VBA editor of Microsoft Office as explained in the article HOWTO: Create an add-in for the VBA editor (32-bit or 64-bit) of Office with Visual Studio .NET.
  • Add two usercontrols named UserControlToolWindow1.vb and UserControlToolWindow2.vb.
  • Add a usercontrol named UserControlHost.vb to the project. This is the common usercontrol that will be hosted by all toolwindows when calling the VBE.Windows.CreateToolWindow method. In turn, this usercontrol will host the specific usercontrol of each toolwindow (UserControlToolWindow1 and UserControlToolWindow2). This approach of using an intermediate usercontrol to host the actual usercontrols has the following advantages:
    • This UserControlHost control is the only one that needs to be Public and COM-registered. Otherwise each usercontrol of each toolwindow should be COM-registered.
    • It provides a centralized workaround for the resizing problem.
    • It provides a centralized workaround for a problem that exists in toolwindows of the VBA editor and VB5 (but not in VB6): keys such as Tab, Return, etc. don't work.
  • Change the code of the Connect.vb file by the code below. The code creates two buttons on the Standard toolbar that shows different toolwindows. When a button is clicked, if the toolwindow is not created yet, it is created. Since when closing toolwindows they are not destroyed, just hidden, if the toolwindow was already created it is made visible.
Language: VB.NET   Copy Code Copy Code (IE only)
Imports MyCompany.Interop
Imports MyCompany.Interop.Extensibility
Imports System.Windows.Forms
Imports System.Runtime.InteropServices
Imports Microsoft.Office.Core
Imports System.Drawing

<ComVisible(True), Guid(PUT_NEW_GUID_HERE), ProgId("MyVBAAddin.Connect")> _
Public Class Connect
   Implements IDTExtensibility2

   Private _VBE As VBAExtensibility.VBE
   Private _AddIn As VBAExtensibility.AddIn
   Private WithEvents _CommandBarButton1 As CommandBarButton
   Private WithEvents _CommandBarButton2 As CommandBarButton

   Private _toolWindow1 As VBAExtensibility.Window
   Private _toolWindow2 As VBAExtensibility.Window

   Private Sub OnConnection(Application As Object, ConnectMode As ext_ConnectMode, AddInInst As Object, _
      ByRef custom As System.Array) Implements IDTExtensibility2.OnConnection

      Try

         _VBE = DirectCast(Application, VBAExtensibility.VBE)
         _AddIn = DirectCast(AddInInst, VBAExtensibility.AddIn)

         Select Case ConnectMode

            Case ext_ConnectMode.ext_cm_Startup
               ' OnStartupComplete will be called

            Case ext_ConnectMode.ext_cm_AfterStartup
               InitializeAddIn()

         End Select

      Catch ex As Exception

         MessageBox.Show(ex.ToString())

      End Try

   End Sub

   Private Sub OnDisconnection(RemoveMode As ext_DisconnectMode, _
      ByRef custom As System.Array) Implements IDTExtensibility2.OnDisconnection

      If Not _CommandBarButton1 Is Nothing Then

         _CommandBarButton1.Delete()
         _CommandBarButton1 = Nothing

      End If

      If Not _CommandBarButton2 Is Nothing Then

         _CommandBarButton2.Delete()
         _CommandBarButton2 = Nothing

      End If

   End Sub

   Private Sub OnStartupComplete(ByRef custom As System.Array) _
      Implements IDTExtensibility2.OnStartupComplete

      InitializeAddIn()

   End Sub

   Private Sub OnAddInsUpdate(ByRef custom As System.Array) _
      Implements IDTExtensibility2.OnAddInsUpdate

   End Sub

   Private Sub OnBeginShutdown(ByRef custom As System.Array) Implements IDTExtensibility2.OnBeginShutdown

   End Sub

   Private Sub InitializeAddIn()

      Dim standardCommandBar As CommandBar
      Dim commandBarControl As CommandBarControl

      Try

         standardCommandBar = _VBE.CommandBars.Item("Standard")

         commandBarControl = standardCommandBar.Controls.Add(MsoControlType.msoControlButton)
         _CommandBarButton1 = DirectCast(commandBarControl, CommandBarButton)
         _CommandBarButton1.Caption = "Toolwindow 1"
         _CommandBarButton1.FaceId = 59
         _CommandBarButton1.Style = MsoButtonStyle.msoButtonIconAndCaption
         _CommandBarButton1.BeginGroup = True

         commandBarControl = standardCommandBar.Controls.Add(MsoControlType.msoControlButton)
         _CommandBarButton2 = DirectCast(commandBarControl, CommandBarButton)
         _CommandBarButton2.Caption = "Toolwindow 2"
         _CommandBarButton2.FaceId = 59
         _CommandBarButton2.Style = MsoButtonStyle.msoButtonIconAndCaption
         _CommandBarButton2.BeginGroup = True

      Catch ex As Exception

         MessageBox.Show(ex.ToString())

      End Try

   End Sub

   Private Function CreateToolWindow(ByVal toolWindowCaption As String, ByVal toolWindowGuid As String, _
      ByVal toolWindowUserControl As UserControl) As VBAExtensibility.Window

      Dim userControlObject As Object = Nothing
      Dim userControlHost As UserControlHost
      Dim toolWindow As VBAExtensibility.Window
      Dim progId As String

      ' IMPORTANT: ensure that you use the same ProgId value used in the ProgId attribute of the UserControlHost class
      progId = "MyVBAAddin.UserControlHost"

      toolWindow = _VBE.Windows.CreateToolWindow(_AddIn, progId, toolWindowCaption, toolWindowGuid, userControlObject)
      userControlHost = DirectCast(userControlObject, UserControlHost)

      toolWindow.Visible = True

      userControlHost.AddUserControl(toolWindowUserControl)

      Return toolWindow

   End Function

   Private Sub _CommandBarButton1_Click(Ctrl As Microsoft.Office.Core.CommandBarButton, _
      ByRef CancelDefault As Boolean) Handles _CommandBarButton1.Click

      Dim userControlObject As Object = Nothing
      Dim userControlToolWindow1 As UserControlToolWindow1

      Try

         If _toolWindow1 Is Nothing Then

            userControlToolWindow1 = New UserControlToolWindow1()

            ' TODO: Change the GUID
            _toolWindow1 = CreateToolWindow("My toolwindow 1", "{PUT_NEW_GUID_HERE}", userControlToolWindow1)

            userControlToolWindow1.Initialize(_VBE)

         Else

            _toolWindow1.Visible = True

         End If

      Catch ex As Exception

         MessageBox.Show(ex.ToString)

      End Try

   End Sub

   Private Sub _CommandBarButton2_Click(Ctrl As Microsoft.Office.Core.CommandBarButton, _
      ByRef CancelDefault As Boolean) Handles _CommandBarButton2.Click

      Dim userControlObject As Object = Nothing
      Dim userControlToolWindow2 As UserControlToolWindow2

      Try

         If _toolWindow2 Is Nothing Then

            userControlToolWindow2 = New UserControlToolWindow2()

            ' TODO: Change the GUID
            _toolWindow2 = CreateToolWindow("My toolwindow 2", "{PUT_NEW_GUID_HERE}", userControlToolWindow2)

            userControlToolWindow2.Initialize(_VBE)

         Else

            _toolWindow2.Visible = True

         End If

      Catch ex As Exception

         MessageBox.Show(ex.ToString)

      End Try

   End Sub

End Class
  • Replace the code of UserControlHost by the code below.
  • Notice that the scope of the usercontrol must be Public, and that it must have a ProgId and be visible to COM.
  • This usercontrol will host the actual usercontrol of the toolwindow (which will have the DockStyle.Fill).
  • It needs to do subclassing with the parent window to detect when the size is changed.
  • It needs to override the ProcessKeyPreview method to detect the Tab, Shift+Tab and Return keys, to make them behave as expected.
Language: VB.NET   Copy Code Copy Code (IE only)
Imports System.Runtime.InteropServices
Imports System.Windows.Forms
Imports System.Drawing

<ComVisible(True), Guid(PUT_NEW_GUID_HERE), ProgId("MyVBAAddin.UserControlHost")> _
Public Class UserControlHost

   Private Class SubClassingWindow
      Inherits System.Windows.Forms.NativeWindow

      Public Event CallBackProc(ByRef m As Message)

      Public Sub New(ByVal handle As IntPtr)
         MyBase.AssignHandle(handle)
      End Sub

      Protected Overrides Sub WndProc(ByRef m As Message)

         Const WM_SIZE As Integer = &H5

         If m.Msg = WM_SIZE Then
            RaiseEvent CallBackProc(m)
         End If

         MyBase.WndProc(m)

      End Sub

      Protected Overrides Sub Finalize()

         Me.ReleaseHandle()

         MyBase.Finalize()

      End Sub

   End Class

   <StructLayout(LayoutKind.Sequential)> _
   Private Structure RECT
      Friend Left As Integer
      Friend Top As Integer
      Friend Right As Integer
      Friend Bottom As Integer
   End Structure

   Private Declare Function GetParent Lib "user32" (ByVal hWnd As IntPtr) As IntPtr
   Private Declare Function GetClientRect Lib "user32" Alias "GetClientRect" (ByVal hWnd As IntPtr, ByRef lpRect As RECT) As Integer

   Private _parentHandle As IntPtr
   Private WithEvents _subClassingWindow As SubClassingWindow

   Friend Sub AddUserControl(ByVal control As UserControl)

      _parentHandle = GetParent(Me.Handle)

      _subClassingWindow = New SubClassingWindow(_parentHandle)

      control.Dock = DockStyle.Fill

      Me.Controls.Add(control)

      AdjustSize()

   End Sub

   Private Sub _subClassingWindow_CallBackProc(ByRef m As System.Windows.Forms.Message) Handles _subClassingWindow.CallBackProc

      AdjustSize()

   End Sub

   Private Sub AdjustSize()

      Dim tRect As RECT

      If GetClientRect(_parentHandle, tRect) <> 0 Then

         Me.Size = New Size(tRect.Right - tRect.Left, tRect.Bottom - tRect.Top)

      End If

   End Sub

   Protected Overrides Function ProcessKeyPreview(ByRef m As System.Windows.Forms.Message) As Boolean

      Const WM_KEYDOWN As Integer = &H100

      Dim result As Boolean = False
      Dim pressedKey As Keys
      Dim hostedUserControl As UserControl
      Dim activeButton As Button

      hostedUserControl = DirectCast(Me.Controls.Item(0), UserControl)

      If m.Msg = WM_KEYDOWN Then

         pressedKey = CType(m.WParam, Keys)

         Select Case pressedKey

            Case Keys.Tab

               If Control.ModifierKeys = Keys.None Then ' Tab

                  Me.SelectNextControl(hostedUserControl.ActiveControl, True, True, True, True)
                  result = True

               ElseIf Control.ModifierKeys = Keys.Shift Then ' Shift + Tab

                  Me.SelectNextControl(hostedUserControl.ActiveControl, False, True, True, True)
                  result = True

               End If

            Case Keys.Return

               If TypeOf hostedUserControl.ActiveControl Is Button Then

                  activeButton = DirectCast(hostedUserControl.ActiveControl, Button)
                  activeButton.PerformClick()

               End If

         End Select

      End If

      If result = False Then
         result = MyBase.ProcessKeyPreview(m)
      End If

      Return result

   End Function

End Class
  • In the UserControlToolWindow1 usercontrol, add a Button named Button1 and some textboxes.
  • Replace the code of the UserControlToolWindow1 by the code below. The code sets the backcolor to red to identify this toolwindow.
  • Notice that the scope of the class has been changed to Friend, it doesn't need to be Public.
  • The usercontrol can store the VBE instance for whatever use.
  • When testing the toolwindow, check that the Tab / Shift+Tab keystrokes change the focus and that the Return key clicks the button, as expected.
Language: VB.NET   Copy Code Copy Code (IE only)
Imports MyCompany.Interop.VBAExtensibility
Imports System.Windows.Forms

Friend Class UserControlToolWindow1

   Private _VBE As VBE

   Friend Sub Initialize(ByVal vbe As VBE)

      Me.BackColor = Drawing.Color.Red

      _VBE = vbe

   End Sub

   Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

      MessageBox.Show("Toolwindow shown in VBA editor version " & _VBE.Version)

   End Sub

End Class
  • In the UserControlToolWindow2 usercontrol, add a Button named Button1 and some textboxes.
  • Replace the code of the UserControlToolWindow2 by the code below. The code sets the backcolor to blue to identify this toolwindow.
  • Notice that the scope of the class has been changed to Friend, it doesn't need to be Public.
  • The usercontrol can store the VBE instance for whatever use.
  • When testing the toolwindow, check that the Tab / Shift+Tab keystrokes change the focus and that the Return key clicks the button, as expected.
Language: VB.NET   Copy Code Copy Code (IE only)
Imports MyCompany.Interop.VBAExtensibility
Imports System.Windows.Forms

Friend Class UserControlToolWindow2

   Private _VBE As VBE

   Friend Sub Initialize(ByVal vbe As VBE)

      Me.BackColor = Drawing.Color.Blue

      _VBE = vbe

   End Sub

   Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click

      MessageBox.Show("Toolwindow shown in VBA editor version " & _VBE.Version)

   End Sub

End Class

Related articles

Top