How to Add Icon to a Excel Menu/Toolbar Button

Question

Hi,

I need to add a image to a custom toolbar/menu item which is create through VBA.

For a toolbar item, I tried following code

Set NewBtn = TBar.Controls.Add(Type:=msoControlButton)
With NewBtn

.Picture = LoadPicture("mypic.bmp")
.OnAction = "'MyFunction""" & para1 & """'"  //VBA Function
'.Caption = "MyFunction"
.TooltipText = "MyFunction"
.Style = msoButtonCaption

End With

In the above code LoadPicture() does not seem to be working. My toolbar is initializing at the workbook load up event. I noticed that the image is loading to the toolbar button, but in a fraction of second it disappears and only item text is displayed. My image is 16x16 pixel bmp one.

Any help appreciate to get around this problem

Thank you

Answer 1

Use MsoButtonStyle.msoButtonIcon or one of the MsoButtonStyle members that contain the word Icon.

Answer 2

In VBA I store the icons on a worksheet (oTemplate) and transfer them to the buttons using:
with NewBtn
oTemplate.Shapes("picCalcOpt").CopyPicture
.PasteFace