Assign non-Face Id icon to a commandbar button?

[Frenchi]

I'm creating an XLA addin. This addin creates a commandbar, button and assign the appropriate macro. I really would like to use by own Icon and NOT a FaceID. How do I accoplish this task? Using Excel 97.
(I did a search on this BB and could not find antyhing to solve this riddle.)
Thanks

 

[Juan Pablo González]

Well, what I've used is to put the bitmap, or whatever, somewhere in the sheet, then, copy it, and use the .PasteFace method of your commandbarbutton.

 

[tim963]

Juan, what would be the best way to format the picture for this use?

 

[Juan Pablo González]

I think its bitmap. Even better if you create/copy the picture in a small format (I think its 16 x 16 pixels), that way, you shouldn't have resizing problems when you paste it.

 

[Frenchi]

Ok I tried. Unfortunately is an Add-in it did not work. I was unable to select the image via VB in the XLA. I have no problem when it is an XLS file.
Thanks

 

[Juan Pablo González]

Don't select it. That's a very common problem with a very easy solution. Just copy directly, without selecting it.

 

[Richie(UK)]

Hi Frenchi,

See if the following routines are of any help in understanding how to add a custom picture to your menu items.

<pre>'===============================================================
Sub AddMenu()
' This procedure adds a custom menu item
Dim ctlCBarControl As CommandBarControl
Dim cbpPopup As CommandBarPopup
Dim cmdButton As CommandBarButton

' create a popup control on the Tools menu
For Each ctlCBarControl In Application.CommandBars _
("Worksheet Menu Bar").Controls("Tools").Controls
If ctlCBarControl.Caption = "MyCustomMenu" Then _
ctlCBarControl.Delete
'already exists so delete first
Next

' Custom menu item added
Set cbpPopup = Application.CommandBars("Worksheet Menu Bar"). _
Controls("Tools").Controls.Add _
(msoControlPopup, , , 3, False)
'3=position in menu, False=Permanent
cbpPopup.Caption = "MyCustomMenu"
cbpPopup.Visible = True

' add item to the submenu
Set cmdButton = cbpPopup.Controls.Add(Type:=msoControlButton)
cmdButton.Visible = True
cmdButton.Style = msoButtonIconAndCaption 'icon and caption
cmdButton.Caption = "MySubMenu"
cmdButton.OnAction = "MyMacro" 'action to perform
Sheet1.Shapes("MyPicture").Copy ' copy the custom icon
cmdButton.PasteFace ' paste the custom icon

Set ctlCBarControl = Nothing
Set cbpPopup = Nothing
Set cmdButton = Nothing
'Release memory from Object variables

End Sub
'===============================================================
'

'===============================================================
Private Sub RemoveMenu()

' If the Custom Menu exists, delete it.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar") _
.Controls("Tools").Controls("MyCustomMenu").Delete

End Sub
'===============================================================
'</pre>

HTH

PS. I find Icon editors useful for creating the
custom button faces as you can specify the 16X16 size.

PPS. In the above examples, your custom picture
is stored on sheet1.