Custom toolbar

[Linda Bennett]

Would someone be kind enough to explain to me how I go about saving a
custom toolbar so it is available for all workbooks?

TIA
LB

--

 

[Dave Peterson]

Instead of saving a customized toolbar, I think it's easier to have a workbook
build it whenever it opens.

And since you want it available to every workbook, you can put it in your
XLStart folder.

John Walkenbach has a very nice program that actually adds some items to the
worksheet menubar--it looks very neat and is easy to update. It's his MenuMaker
program:

http://j-walk.com/ss/excel/tips/tip53.htm

 

[Dave Peterson]

And if you really want a toolbar, here's some code that I use as a shell:

In a general module:

Option Explicit
Sub create_menubar()

Dim i As Long

Dim mac_names As Variant
Dim cap_names As Variant
Dim tip_text As Variant

Call remove_menubar

mac_names = Array("mac1", _
"mac2", _
"mac3")

cap_names = Array("caption 1", _
"caption 2", _
"caption 3")

tip_text = Array("tip 1", _
"tip 2", _
"tip 3")

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

For i = LBound(mac_names) To UBound(mac_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & mac_names(i)
.Caption = cap_names(i)
.Style = msoButtonIconAndCaption
.FaceId = 71 + i
.TooltipText = tip_text(i)
End With
Next i
End With
End Sub

Sub remove_menubar()
On Error Resume Next
Application.CommandBars("Test99").Delete
On Error GoTo 0
End Sub


Under Thisworkbook:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call remove_menubar
End Sub

Private Sub Workbook_Open()
Call create_menubar
End Sub

====
The Mac_names, cap_names, and tip_text are set up for 3 elements. But just
delete/add from each of these and the code will loop through them (even if
there's just one) to add buttons to a temporary toolbar.

(make sure you have the same number of elements for each array.)