Custom CommandbarButton Faces in VBA

Mark Garratt suggests this post – thanks for the suggestion.

You can use just about any picture as the face of a commandbarbutton. The better the picture looks when it’s small, the better it will look on your commandbar, so using that hot Jennifer Garner photo may be a bit much for a tool face. Tool faces are 16 x 16 and you can see what your image will look like by converting it in your image editing software. Or, if you’re like me, you just put on a toolbar in Excel and see what it looks like.

To get that picture on a tool, you first need to put in on a worksheet that will be accessible when the code is run. I generally put in on a hidden sheet in the same workbook as the code. Use Insert > Picture to get the picture on the worksheet.

Now that you have access to the picture, you can use the PasteFace method of the CommandBarButton object. You must first copy the picture into memory. This example creates a commandbar and a single button with a custom face.

Sub CreateCommandBar()
   
    Dim cb As CommandBar
    Dim cbb As CommandBarButton
   
    Set cb = Application.CommandBars.Add(“MyBar”)
   
    Set cbb = cb.Controls.Add(msoControlButton)
   
    With cbb
        .OnAction = “RunMySub”
        .TooltipText = “Click me to erase hard drive”
        .Style = msoButtonIcon
        Sheet1.Pictures(“Picture 1”).Copy
        .PasteFace
    End With
   
    cb.Visible = True
   
End Sub

CustomFace1

Mark also has a way to get at images that are stored externally. He uses the AddPicture method to get the picture into the sheet.

Sub CreateCommandBar()
   
    Dim cb As CommandBar
    Dim cbb As CommandBarButton
    Dim imgTool As Shape
   
    Const sFILE = “C:MyButton.gif”
    Const sNAME = “MyToolFace”
   
    Set cb = Application.CommandBars.Add(“MyBar”)
    Set cbb = cb.Controls.Add(msoControlButton)
   
    With Sheet1
        On Error Resume Next
            Set imgTool = .Shapes(sNAME)
        On Error GoTo 0
       
        If imgTool Is Nothing Then
            Set imgTool = .Shapes.AddPicture(Filename:=sFILE, _
                linktofile:=msoFalse, _
                savewithdocument:=msoTrue, _
                Left:=.Cells(1, 1).Left, _
                Top:=.Cells(1, 1).Top, _
                Width:=10, Height:=10)
            imgTool.Name = sNAME
        End If
    End With
   
    With cbb
        .OnAction = “RunMySub”
        .TooltipText = “Click me to erase hard drive”
        .Style = msoButtonIcon
        Sheet1.Pictures(sNAME).Copy
        .PasteFace
    End With
   
    cb.Visible = True
   
End Sub
Posted in Uncategorized

6 thoughts on “Custom CommandbarButton Faces in VBA

  1. There is nothing more annoying than starting Excel to paste some data only to find that an add-in has overwritten the clipboard with a tiny icon graphic.

    I’ve deleted excellent add-ins for this reason alone.

    Starting with Excel XP, you don’t need to use PasteFace
    This Microsoft Knewledgebase article describes how:
    http://support.microsoft.com/kb/286460

    Rob

  2. Rob,

    That article is good if you don’t mind multiple image files being installed with the addin. Do you know if pictures saved in the addin can use this method (ie stored as shapes on the spreadsheet)?

    Wilson.

  3. I know this is an old post but I can’t seem to get this to work and I have no where else to ask

    For some reason, I can’t run the following line:
    Sheet1.Pictures(“Picture 1?).Copy

    The Error I receive is “Unable to get the pictures property of the worksheet class”

    How can I fix this?

  4. can you tell me what you did to fix the “Unable to get pictures… ” error, as I am having the same problem

    thanks


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.