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
toolbar.-- Regards,Tom Ogilvy"MarkHG" <[email protected]> wrote
in message news:[email protected]...
> 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
>

[sebastienm]

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

"MarkHG" wrote:

> 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
>
>

[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 wrote:
>
> 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
> toolbar.-- Regards,Tom Ogilvy"MarkHG" <[email protected]> wrote
> in message news:[email protected]...
> > 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
> >

--

Dave Peterson

[Tom Ogilvy]

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

Thanks.
--
Regards,
Tom Ogilvy

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
> >
> > 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
> > toolbar.-- Regards,Tom Ogilvy"MarkHG" <[email protected]>
wrote
> > in message news:[email protected]...
> > > 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
> > >
>
> --
>
> Dave Peterson

[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

--
Regards,
Tom Ogilvy

"MarkHG" <[email protected]> wrote in message
news:[email protected]...
> Just checked - no Picture property in XL2000 - so back to square one
> really.
>
> Thanks anyway.
>