Custom toolbar - how to save in file and show on demand?

[igivanovg]

What I want:
- create a custom toolbar with some buttons, attach macros to the
buttons and save the whole thing in an .xls file.
- to be able to use the file with my program (does excel automation),
i.e. programmatically open the file and display the toolbar, but only
when I say so.

What happens: I open a file, create a toolbar, buttons, macros and
save the file, e.g. Toolbars.xls, close Excel. Then whenever I open
Excel with ANY file, the darned thing appears. Attach or no attach -
doesn't matter. When I copy Toolbars.xls to another machine and open
it, the toolbar appears but w/o any buttons. After that this empty
toolbar appears when I open ANY file on that machine.

This behaviour is nonsensical and counterintuitive. I did a similar
thing with Word and it all worked as expected, i.e. I saved macros and
buttons a Toolbars.doc file, then opened it whenever I wanted and
attached it to whichever doc file I wanted on any computer. And it
didn't just show up on its own.

The help file is of no help.

It's Excel 2000/97, but I'll need it with any Excel 97+

Please help!

Thanks

 

[igivanovg]

What I want:

[...]

Note: I have no problem with creating/deleting a toolbar and buttons
and attaching macros programmatically on the fly. So this would solve
most of the problem, but there seems to be no way to put custom images
on buttons.

 

[Dave Peterson]

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

In xl2007, those toolbars and menu modifications will show up under the addins.

===========

Here's a modified version of the toolbar code from Debra Dalgleish's site.
You'll have to add a worksheet with the pictures on it. (Hide that worksheet
later???)

Option Explicit
Public Const ToolBarName As String = "MyToolbarName"
Sub Auto_Open()
Call CreateMenubar
End Sub
Sub Auto_Close()
Call RemoveMenubar
End Sub
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0
End Sub
Sub CreateMenubar()

Dim iCtr As Long

Dim MacNames As Variant
Dim CapNames As Variant
Dim TipText As Variant
Dim PictNames As Variant
Dim PictWks As Worksheet

Call RemoveMenubar

MacNames = Array("aaa", _
"bbb")

CapNames = Array("AAA Caption", _
"BBB Caption")

TipText = Array("AAA tip", _
"BBB tip")

PictNames = Array("Pic1", "Pic2")

Set PictWks = ThisWorkbook.Worksheets("Pictures")

With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

For iCtr = LBound(MacNames) To UBound(MacNames)
PictWks.Pictures(PictNames(iCtr)).Copy
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNames(iCtr)
.Style = msoButtonIconAndCaption
.PasteFace
.TooltipText = tip_text(iCtr)
End With
Next iCtr

End With
End Sub
Sub AAA()
MsgBox "aaa"
End Sub
Sub BBB()
MsgBox "bbb"
End Sub

What I want:

[...]

Note: I have no problem with creating/deleting a toolbar and buttons
and attaching macros programmatically on the fly. So this would solve
most of the problem, but there seems to be no way to put custom images
on buttons.

 

[igivanovg]

[snip]

Thanks for the example Dave (and thanks go to the original author(s)
as well)!

The trick with using pictures from a worksheet is neat. I couldn't
find a way in Excel to fill in the clipboard with a file contents for
further use in PasteFace, so I was planning to do it outside (the main
application is in Delphi where I can easily paste an image to the
clipboard). This may simplify my code a bit.

Regardless, the toolbar behaviour in Excel is completely weird.