HOWTO: Create a toolwindow for the VBA editor of Office from an ad... https://web.archive.org/web/20211024050815/https://www.mztools.com/...

The Wayback Machine - https://web.archive.org/web/20211024050815/https://www.mztools.com/articles/2012/MZ2012017.aspx

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 (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

1 sur 6 11/10/2023, 21:07

HOWTO: Create a toolwindow for the VBA editor of Office from an ad... https://web.archive.org/web/20211024050815/https://www.mztools.com/...

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

2 sur 6 11/10/2023, 21:07

HOWTO: Create a toolwindow for the VBA editor of Office from an ad... https://web.archive.org/web/20211024050815/https://www.mztools.com/...

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 (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)

3 sur 6 11/10/2023, 21:07

HOWTO: Create a toolwindow for the VBA editor of Office from an ad... https://web.archive.org/web/20211024050815/https://www.mztools.com/...

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

4 sur 6 11/10/2023, 21:07

HOWTO: Create a toolwindow for the VBA editor of Office from an ad... https://web.archive.org/web/20211024050815/https://www.mztools.com/...

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 (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 (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

5 sur 6 11/10/2023, 21:07

HOWTO: Create a toolwindow for the VBA editor of Office from an ad... https://web.archive.org/web/20211024050815/https://www.mztools.com/...

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

IMPORTANT: After building the project for the first time without errors, you need to register the dll as COM component (the Connect and
UserControlHost public types) using the regasm.exe tool with the /codebase argument 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.

Related articles

HOWTO: Create an add-in for the VBA editor (32-bit or 64-bit) of Office with Visual Studio .NET
HOWTO: Adding buttons, commandbars and toolbars to the VBA editor of Office from an add-in with Visual Studio .NET

Copyright © 2021 MZTools Software. All Rights Reserved. Legal Notice

6 sur 6 11/10/2023, 21:07