Toobar question - how 2 get other user to see same toolbar

M

mike_mike

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)
 
M

mike_mike

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
 
M

mike_mike

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
 
Top