Another Toolbar Question

Can I attach a toolbar to an excel add-in (.xla). If so is there something special i have to do because I can't seem to get it to let me.

You need to have the toolbar get loaded in the Add-In's Workbook_Open event so that it will be there every time.

Right, but can how do I attach the toolbar to the add-in. Right now I've got it programmed in through code but I'd like to customize the pics on the buttons and I'm not sure how to do that other than just using Excel's toolbar builder dialog box. This needs to be redistributable so I need to figure out how I can redistribute the toolbar. with workbooks, you can just go to Customize, and attach it to the workbook but that method isn't allowed it doesn't look like with add-ins. I don't mind coding the whole thing if there is a way to customize the pics...is there a grid or something you can get to through code to change them?

Look at this post (http://www.visualbasicforum.com/bbs/showflat.php?Cat=&Board=vba&Number=48118&page=0&view=collapsed&sb=5&o= 7&part=) to help get yourself started.

It will be a little different for you since I was making menubars and you're making a toolbar, but the differences should be pretty minor.

Yeah, I've got all that stuff, and am using that right now. I guess I'm not being clear enough with my question. I want to customize the Face of the button. I can't find a FaceId that describes well enough what the button does. Is there a way to draw the picture in code?

Ahhhhh... sorry about that.

If there's a way to customize the button face, then I don't know what it is. The way I've handled it in the past is to use a button that is just text. You can do that like this:

.Style = msoButtonCaption
.Caption = "Format..."

Other than that, I don't think I can help you.

We have run into the same issue since MS keeps changing the default "gray" color for Windows. We have come to the conclusion that we'd need to load the bitmap image and then use the Windows API (GDI) call to fill the region in the background with the current 3DButtonFace color. We have not yet deemed it as "worth the effort" to do this.

Toolbars cannot be "attached" through code. They can only be attached manually. This is done by choosing Alt | Tools | Customize... Then clicking on the Toolbars Tab and then clicking on the <Attach> button. Then choose which Toolbar that you wish to "attach" to the Workbook. Then Save the Workbook.

Note that CommandBars("MyCommandBar").Delete should be called when Excel exits, or the next time the XLA is opened, the old version of the CommandBar will remain and any updates to the XLA's CommandBar will be ignored since a CB of the same name will already exist within the Excel.xlb.

Better is for the XLA's Workbook_Open() Event to create the CommandBar on the fly each time, and make use of Temporary:=True when you call CommandBars.Add() that way you don't even have to worry about deleting it when Excel exits.

To load a Bitmap from a file, MSFT help on the Button.Picture Property shows the following 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 = picButton

'Use the second image to define the area of the
'button that should be transparent.
.Mask = picMask
End With
End Sub Hope this helps?
Mike

I've been using this for years:
http://www.j-walk.com/ss/excel/tips/tip67.htm