ThreeWave Placing A Picture On A CommandBar Item

This page describes two methods for putting a custom picture on a command bar item.
ShortFadeBar

Introduction

If you are creating custom commandbar buttons or menu items for your application, you may want to use custom images rather than the built-in images accessible via the FaceID property. This page describes how to use either an external file for the image or an image embedded on a worksheet.

To use an external file, use code like the following. 

Sub ImageFromExternalFile()
    Dim Btn As Office.CommandBarButton
    Set Btn = Application.CommandBars.FindControl(ID:=30007) _
        .Controls.Add(Type:=msoControlButton, temporary:=True)
    With Btn
        .Caption = "Click Me"
        .Style = msoButtonIconAndCaption
        .Picture = LoadPicture("C:\TestPic.bmp")
    End With
End Sub

This code creates a new menu item on the Tools menu. In order to use a picture on the item, you must set the Style property to a value that supports an image. The code calls the LoadPicture function to assign the image in the file C:\TestPic.bmp to the menu item.

If you do not want to distribute separate image files with your application, you can place the images on a worksheet (perhaps hidden) and load those images on to the command bar items. Use code like the following: 

Sub ImageFromEmbedded()
    Dim P As Excel.Picture
    Dim Btn As Office.CommandBarButton
    Set Btn = Application.CommandBars.FindControl(ID:=30007) _
        .Controls.Add(Type:=msoControlButton, temporary:=True)
    Set P = Worksheets("Sheet1").Pictures("ThePict")
    P.CopyPicture xlScreen, xlBitmap
    With Btn
        .Caption = "Click Me"
        .Style = msoButtonIconAndCaption
        .PasteFace
    End With
End Sub

This code creates a new item on the Tools menu and assigns to it the picture named ThePict that resides on Sheet1. The CopyPicture method loads the picture into the clipboard and the PasteFace pastes that picture on to the menu item. The Excel.Picture object type is considered obsolete so you will not see it in the Object Browser unless you choose the Show Hidden Members option. Even though it is considered obsolete, it is fully supported and fully functional.

These example use menu items rather than command bar buttons. The code for command bar buttons is identical.

ShortFadeBar
LastUpdate This page last updated: 23-Feb-2009.

-->