Logo
HOWTO: Create a button with a custom picture 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 add a CommandBarButton with a custom, transparent, picture from an add-in for the VBA editor of Office using Visual Studio .NET and the .NET Framework.

More information

The CommandBarButton class provides de Picture property (for the picture) and the Mask property (to define the pixels of the picture that should be transparent, using the white color for transparent pixels and the black color for non-transparent pixels). However, those properties are COM-based pictures (Stdole.IPictureDisp). This article shows how to:

  • Get a Stdole.IPictureDisp from a .NET System.Drawing.Bitmap.
  • Generate a Mask bitmap automatically from a Picture bitmap, given a known color to make it transparent.

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 and use this code in the Connect.vb class.
  • In the Solution Explorer, right-click in the project node and click the "Add", "New Item..." menu entry.
  • Select the "Resources File" template, that will be named Resource1.resx. Note: this sample will use a resources file to get the .NET bitmap, but you can use an ImageList, etc. By default the resources file is embedded with the assembly dll (select the Resource1.resx file in the Solution Explorer, show the Properties window and see the "Build Action" property set to the "Embedded Resource" value).
  • In the Resource1.resx document window, click the first dropdown and change it from "Strings" to "Images".
  • Click the "Add Resource" dropdown and click "New Image", "BMP Image...". Name the new resource "Image1".
  • In the Properties window change the dimensions of the bitmap to 16x16, and the colors from 8-bit to 24-bit.
  • Draw the bitmap using the Magenta color (RGB=255,0,255) for the transparent areas. Note: you can use any color as the transparent color, as long as you change the color in the code below (in the call to the GetMaskPicture method).
  • Change the code of the Connect.vb file by the code below. The code creates a button with a custom picture at the end of the Standard toolbar.
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 _CommandBarButton As CommandBarButton

   Private Class ImageToPictureDispConverter
      Inherits AxHost

      Friend Sub New()
         MyBase.New("{63109182-966B-4e3c-A8B2-8BC4A88D221C}")
      End Sub

      Friend Function GetIPictureDispFromImage(ByVal img As Image) As Stdole.IPictureDisp

         Dim picture As Stdole.IPictureDisp

         picture = CType(AxHost.GetIPictureDispFromPicture(img), Stdole.IPictureDisp)

         Return picture

      End Function

   End Class

   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 _CommandBarButton Is Nothing Then

         _CommandBarButton.Delete()
         _CommandBarButton = 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 Function GetMaskPicture(ByVal pictureBitmap As Bitmap, ByVal transparentColor As Color) As Bitmap

      Dim maskBitmap As Bitmap
      Dim pixelColor As Color
      Dim width As Integer
      Dim height As Integer
      Dim x As Integer
      Dim y As Integer
      Dim transparentColorARGB As Integer

      transparentColorARGB = transparentColor.ToArgb

      width = pictureBitmap.Width
      height = pictureBitmap.Height

      maskBitmap = New Bitmap(width, height, Imaging.PixelFormat.Format24bppRgb)

      For x = 0 To width - 1

         For y = 0 To height - 1

            pixelColor = pictureBitmap.GetPixel(x, y)

            If pixelColor.ToArgb = transparentColorARGB Then

               maskBitmap.SetPixel(x, y, Color.White) ' Transparent pixel

            Else

               maskBitmap.SetPixel(x, y, Color.Black) ' Non-transparent pixel

            End If

         Next

      Next

      Return maskBitmap

   End Function

   Private Sub InitializeAddIn()

      Dim standardCommandBar As CommandBar
      Dim commandBarControl As CommandBarControl
      Dim maskBitmap As Bitmap
      Dim pictureBitmap As Bitmap
      Dim imageToPictureDispConverter As ImageToPictureDispConverter

      Try

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

         commandBarControl = standardCommandBar.Controls.Add(MsoControlType.msoControlButton)

         _CommandBarButton = DirectCast(commandBarControl, CommandBarButton)

         _CommandBarButton.Caption = "My button"
         _CommandBarButton.BeginGroup = True

         imageToPictureDispConverter = New ImageToPictureDispConverter()

         ' Get a .NET System.Drawing.Bitmap
         pictureBitmap = My.Resources.Resource1.Image1

         ' Get the Mask
         maskBitmap = GetMaskPicture(pictureBitmap, Color.Magenta)

         ' Convert the .NET bitmaps to OLE IPictureDisp and assign the picture and mask
         _CommandBarButton.Picture = imageToPictureDispConverter.GetIPictureDispFromImage(pictureBitmap)
         _CommandBarButton.Mask = imageToPictureDispConverter.GetIPictureDispFromImage(maskBitmap)

         imageToPictureDispConverter.Dispose()

      Catch ex As Exception

         MessageBox.Show(ex.ToString())

      End Try

   End Sub

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

      MessageBox.Show("Clicked " & Ctrl.Caption)

   End Sub

End Class

Related articles

Top