Sleeper: Custom Icons in Excel

[Scottie P]

I have probably gotten this answer before, but cannot remember.
I have a Custom Commandbar for gathering earthquake data on the web - there are submenu items for gathering, formatting, etc and everything works great.

What I want to do is get 'custom' icons onto the dropdown context menu. Not icons that were modified from inherent images in MS Office (such as the Customize > Custom Button > then right-clicking the smilie button to Edit it); I have icons that were created in an icon making software that I would like to use for the submenu items,
Mmm...is this possible? I have cruised the Newsgroups and some general searching on Google but haven't found the answer to this specific use of 'outsourced' icons for an Excel App.

I want to use the custom icons in the Excel app - don't care how I need to do it really but I could use some help now.

**Since I will be sending this app to at least two other people, I need the icons to go with the Application (which means that they will need to be stored in the same directory as the XLS, correct?)**

Thanks for having a look!

Scott

[Tommy]

If you can copy it to the clipboard you can .PasteFace but it HAS to be the correct size. The way I do it in VB is to Clipboard.SetData LoadResPicture(101, vbResBitmap) then .PasteFace. The only other way (I haven't tested just a guess) is to put the icon in a cell, copy it to the clipboard, and paste back to the button.

Hmmmmm umm I could write a dll/exe that would accept a location(full path) to the icon, load it in the clipboard, and let you handle it from there? maybe?

[Tommy]

I got it to work with this:

The below is a sample, this not the full code or sub or function.

If MyButton Is Nothing Then
Set MyButton = Application.CommandBars(TOOLBAR_NAME).Controls.Add(1)
End If
With MyButton
.Caption = "Enter Data"
.Style = msoButtonIconAndCaption 'icon and caption 
'.Style = msoButtonCaption 'caption only
'.Style = msoButtonIcon 'icon only
.Tag = "VBA CustomButtons"
.OnAction = "ShowFillIn"
.FaceId = 0
.Visible = True
End With
MyButton.PasteFace

The icons need to be bmp with a size of 16x24
run the program with the bmp file as such
CopybmpToClipboard.exe "C:\Program Files\Microsoft Visual Studio\VB98\somebitmap.bmp"

then pasteface

[TonyJollans]

You don't want or need to be shipping icon files along with the workbook (unless you are offering a facility for the user to pick their own). If your custom commandbar is stored in the workbook with the icons in place there's nothing else you need to do. If you build the commandbar when the workbook opens you will need to have them available. As Tommy says you could have them in cells, or shapes, on a hidden worksheet, or as faces on controls on another custom commandbar which remains invisible. Whatever you choose, it's almost certainly better to do a one-off manual job at the beginning rather than trying to import them every time.

[Scottie P]

Tommy and Tony - Thanks
I am going to have a go at this later and see how it goes. It all sounds likely and reasonable enough to me.

Scott