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)

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

Thanks muchly you are a legend!

I have modified the procedure to add a button, only if it dosen't exist on a
tool bar.

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

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