6

I am developing a custom ribbon extension for Excel, in which a control requires different custom images. I managed to use some images located in my filesystem, but I would like to embed these images inside the .xlsm file. Is it possible to do it and to reference them from the VBA code that updates the image of the control?

For test purposes, this is the XML that defines my custom ribbon:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="ribbonLoaded">
  <ribbon>
    <tabs>
      <tab idMso="TabHome" >
        <group id="customGroup1" label="My Group" insertAfterMso="GroupFont">
          <button id="customButton1" label="Click Me" size="large" onAction="Macro1" getImage="getButtonImage"/>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

And this is the macro that change the image of the customButton1 control:

Dim imgIndex As Long

Public Sub getButtonImage(ByVal control As IRibbonControl, ByRef Image)
Select Case control.ID
  Case "customButton1"
    Set Image = LoadPicture("img" + Trim(Str(imgIndex)) + ".bmp")
    imgIndex = (imgIndex + 1) Mod 2
  End Select
End Sub

I tried to add the bmp files inside the .xlsm and reference them updating the relationships file (.rels), but I don't know how to reference them from VBA and most important, when I open the file with Excel and save it, they are automatically deleted...

Any help is appreciated!

Community
  • 1
  • 1
Rusty Gear
  • 455
  • 1
  • 4
  • 11
  • Do you need to add a few images once or do you need to update the file with new images several times? If you only need to add them once you can add them into the customUI XML by using the [Custom UI Editor](http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/07/7293.aspx). If you need to update them often, that method might not work as well. – Olle Sjögren Oct 02 '12 at 08:23
  • I only need to add the images once. The Custom UI Editor was very useful to solve the first problem of embedding them (manually I was referencing the images in the wrong .rels file...). But how can they be accessed and loaded in VBA? I think the LoadPicture function does not work with them... – Rusty Gear Oct 02 '12 at 09:06

2 Answers2

1

If the image is embedded in the customUI, you do not need VBA to add them to a control. Just use the same ID for the image in an image tag:

<button id="button1" label="Test" size="large" image="TestID" onAction="ButtonOnAction" />

My sample is adressing the image with ID "TestID", which must be found in the customUI XML - expand the customUI node in the Custom UI Editor to find or change the image ID (or use the editor to add a new image).

Olle Sjögren
  • 5,315
  • 3
  • 31
  • 51
  • 1
    Thanks Olle. One step closer! But how can I do if I need to dynamically change the image of the button choosing from all the embedded images? The `getButtonImage` in my question shows what I want to achieve: cycling through a set of images (only 2 in the example, but they can be up to 256...) every time the user clicks the button. – Rusty Gear Oct 02 '12 at 10:03
  • 1
    Ah, thats why I asked "once or several times" in the comment to your question, but I see now my question was a little unclear. I need to test this, but I'm afraid I don't have the time at the moment. I will get back to you if I solve it! – Olle Sjögren Oct 02 '12 at 10:22
  • 1
    I found [this link](http://www.rondebruin.nl/getimage.htm) from Ron de Bruin. Please, see the examples under the heading called _Change Image with VBA after you open the file_ for a possible way forward. It's the best I can do for you today! – Olle Sjögren Oct 02 '12 at 10:32
  • 1
    Yes, the example 3 is exactly what I am looking for! Unfortunately the conclusion is that it is not possible to load an image from the Excel file, and the only workaround is to decompress it temporarily in order to access the image files... :-( – Rusty Gear Oct 02 '12 at 11:09
  • I've been trying a different approach using CustomXMLParts, but I'm afraid I can't get it to work. I can load images from disk into custom xml in the file and I can take those images and save them back to disk - but I can't use them in the `getImage` callback. – Olle Sjögren Oct 09 '12 at 07:45
  • I found [this old post](http://www.pcreview.co.uk/forums/getimage-and-vba-callback-t3608855.html) from 2008, where the conclusion seems to be that it can't be done - maybe someone else has new information? – Olle Sjögren Oct 09 '12 at 07:46
1

robcooper's answer from UtterAccess.com might help:

Public Sub getButtonImage(ByVal control As IRibbonControl, ByRef image)
'for use in Access 2007 Ribbon control
'requires a reference to the Micrsoft Office 12.0 Object Library
    Select Case control.ID
    Case "cmdMainMenu"
        Set image = LoadPicture(CurrentProject.Path & "\home.bmp")
    End Select
End Sub
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
Ray Schröter
  • 11
  • 1