Thanks muchly!
This proc adds a button..if not exists
Sub add_menubar_buttons(stoolbar As String)
Dim i As Long
Dim mac_names As Variant
Dim cap_names As Variant
Dim tip_text As Variant
On Error GoTo Err_han
' buttons to add
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(stoolbar)
For i = LBound(mac_names) To UBound(mac_names)
Dim j As Integer
' Search for a button with same tool text,
' If button not exists then create button.
' Note : buttons don't seem to have a name property
' you could also search on the tag field, or onAction.
Dim bfound As Boolean
bfound = False
For j = 1 To .Controls.Count
If .Controls(j).TooltipText = tip_text(i) Then
bfound = True ' button already exists
Exit For
End If
Next j
If bfound = False Then ' add button to end
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & mac_names(i)
.Caption = cap_names(i)
.Style = msoButtonIconAndCaption
.FaceId = 71 + i
.TooltipText = tip_text(i)
.Tag = tip_text(i)
End With
End If
Next i
End With
Exit Sub
Err_han:
If Err.Number = 5 Then
MsgBox "Toolbar " & stoolbar & " does not exist."
Exit Sub
End If
MsgBox Err.Number
MsgBox Err.Description
End Sub
"Dave Peterson" wrote:
I would create the toolbar when the workbook opens and delete it when the
workbook closes.
Here's how I do it:
http://groups.google.co.uk/groups?th...5B41%40msn.com
If you want to add items to the worksheet menu bar, you can use John
Walkenbach's menumaker:
http://j-walk.com/ss/excel/tips/tip53.htm
mike_mike wrote:
Hi
I have a shared spreadsheet that is used by a number of users. I want to
update the spreadsheet and add a new button to the toolbar SO THAT THEY ALL
SEE IT.
My question is ..How do you get the other users to get the new modified
toolbar without going around to each users desktop and changing it.
I can update the spreadsheet - ie set it to unshared, temporarily to make
changes
mike (oz)
--
Dave Peterson