Here's one that uses pictures on a worksheet -- nicely named!
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
The AAA and BBB subs are just stubs. You can put your macro code that does the
real work there--or call your macros from them.
ker_01 wrote:
>
> Thank you Dave- it looks like an add-in will be the best way to deploy my
> toolbar. I'll use code to install and load (and unload) the add-in from my
> actual workbook.
>
> The one piece I'm still struggling with is whether there is any way to keep
> the custom button icons that I've already developed. I edited my icons
> within the Excel icon editor, so I don't have them saved anywhere as a
> separate file. Is my only option to recreate each 16x16 (bitmap) icon in an
> image editing program, or is there any way to grab a button image (perhaps
> by VBA) and save that image to the worksheet so I can copy it into my new
> add-in?
>
> Thanks for any advice,
> Keith
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> >
> > ker_01 wrote:
> >>
> >> I'm working on a custom toolbar for a workbook I'm building (plenty of
> >> buttons, including custom icons). We've decided to stick the workbook on
> >> the
> >> network and have other folks access it as well. What is the preferred
> >> method
> >> for ensuring the toolbar is available for all users when they open the
> >> workbook?
> >>
> >> It's been years since I had to deploy a custom toolbar, and that was in
> >> Word97 via a template- whereas this is in Excel2003, and it is an active
> >> workbook (not a template). I googled and found a lot about building a
> >> custom
> >> toolbar via code, but there were references to issues with custom icons,
> >> and
> >> I'm hoping there is a way to just keep the toolbar connected to the
> >> workbook
> >> without having to generate it via code for each user.
> >>
> >> Thanks for any advice,
> >> Keith
> >
> > --
> >
> > Dave Peterson
--
Dave Peterson
Similar Threads
