Adding custom faces-icons to toobar buttons.

[MarkHG]

Hi

I'm developing some addins that have functionality accessed from a
custom toolbar.

I want to be able to add a button to the toolbar with an icon of my own
- not using one of the built in FaceID's.

Is there an easy way of doing this in code?

I need to be able to generate the Toolbar dynamically depending on
wether the addin is loaded ot not - so manually editing the image, in
the toolbar, which gets persisted in the Excel.xlb file, is not the
ideal solution.

If anybody has worked this out I would be very grateful to here of any
suggestions.

(I have worked out a way of doing it but it is causing other issues
which I can't seem to get around).

Thanks

Mark

 

[Tom Ogilvy]

In xl2002 and later, the button has a picture property.

The help example:

Sub ChangeButtonImage() Dim picPicture As IPictureDisp Dim picMask As
IPictureDisp Set picPicture = stdole.StdFunctions.LoadPicture( _
"c:\images\picture.bmp") Set picMask = stdole.StdFunctions.LoadPicture( _
"c:\images\mask.bmp") 'Reference the first button on the first command
bar 'using a With...End With block. With
Application.CommandBars.FindControl(msoControlButton) 'Change the
button image. .Picture = picPicture 'Use the second image to
define the area of the 'button that should be transparent.
..Mask = picMask End WithEnd SubOr you can use copy , then pasteface. In
this case, the image would have to be stored on a hidden sheet (for
example). Some other suggestions have been to build a toolbar with the icons
you want and attach it to the file. Then you can copy the icons from that

 

[Guest]

Hi,
Maybe there is a simpler way, not sure:
You can load a picture into a sheet, copy it, and paste onto the menu or
command bar button:
Dim wsh As Worksheet 'sheet where picture loads
Dim p As Picture
Dim c As CommandBarButton

Set wsh = ActiveSheet
Set p = ActiveSheet.Pictures.Insert("E:\My Pictures\291937c.jpg")
p.CopyPicture
Set c = Application.CommandBars("My Command Bar").Controls(1)
c.PasteFace
p.delete
Try to load only icons-size pix because the loading process takes quite some
time with l;arge picture.

Regards,
Sebastien

 

[Dave Peterson]

Wraptext problem:

Option Explicit

Sub ChangeButtonImage()
Dim picPicture As IPictureDisp
Dim picMask As IPictureDisp
Set picPicture = stdole.StdFunctions.LoadPicture( _
"c:\images\picture.bmp")
Set picMask = stdole.StdFunctions.LoadPicture( _
"c:\images\mask.bmp")

'Reference the first button on the first commandbar
'using a With...End With block.
With Application.CommandBars.FindControl(msoControlButton)
'Change the button image.
.Picture = picPicture
'Use the second image to define the area of the
'button that should be transparent.
.Mask = picMask
End With
End Sub

 

[Tom Ogilvy]

Guess that's what's great about HTML help, it screws things up even when you
pass it through notepad.

Thanks.

 

[MarkHG]

Thanks for that Tom - I think it will work in XL2000 as well
(hopefully).

I was using the copy and pasteface methods but was having problems with
Excels clipboard when switching between workbooks - I was wanting to
redraw the toolbar enabling/disabling functionality depending on what
type of workbook was open. I was refreshing the toolbar on the
sheet_activate events - the copy and paste face was messing up Excels
clipboard. I tried storing the clipboard contents in a DataObject
before copy the images then putting it back in but this was crashing
Excel.

 

[MarkHG]

Just checked - no Picture property in XL2000 - so back to square one
really.

Thanks anyway.

 

[Tom Ogilvy]

the dataobject only holds text strings.

You just need to put your images on a worksheets, then you can select them,
do copy, then use pasteface to put them on you toolbar. Alternately, you
can put them on a dummy toolbar attached to your workbook and do copyface to
get them and pasteface to transfer them.

Sub Test()
Dim cbrMenuCtrl As CommandBarButton
' image on worksheet named Init, first shape in the shapes
' collection for example (or give it a name)
Worksheets("Init").Shapes(1).Copy

Set cbrMenuCtrl = Application.CommandBars("Tools").Controls.Add
With cbrMenuCtrl
.Caption = "Hello"
.OnAction = ThisWorkbook.Name & "!blabla"
.PasteFace
End With

End Sub