customized toolbar issue

[Tami]

i have created worksheet with a customize toolbar with assigned macros to the
buttons.

This file will be a master file. Users will open, save as using a different
name.

however, when i updat the master with the toolbar in the master file and
save, other users cannot see my button changes when they open the master.

we tried to delete the toolbar from the workbook and re-attach the latest
toolbar and yes, the user sees the latest toolbar howeverthe assigned macros
are still referring to an older versin of that file.

help anyone? please...

 

[Bernie Deitrick]

Tami,

Assuming Pre-XL 2007, since you say toolbar and not ribbon...

The best option is to create the commandbar (toolbar) on the fly, when the
workbook is
opened, and delete the commandbar when the workbook is closed. Follow these
instructions and example code.

In the workbook's Thisworkbook object code module, place the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandbar
End Sub

Private Sub Workbook_Open()
CreateCommandbar
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
On Error GoTo NotThere
Application.CommandBars("My Bar").Visible = True
Exit Sub
NotThere:
CreateCommandbar
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
Application.CommandBars("My Bar").Visible = False
End Sub

In a regular code module, place the following:

Dim myBar As CommandBar
Dim myButton As CommandBarButton

Sub CreateCommandbar()

On Error Resume Next
DeleteCommandBar

Set myBar = Application.CommandBars.Add("My Bar")
With myBar
..Position = msoBarTop
..Visible = True
..Enabled = True
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
..Caption = "Hello"
..Style = msoButtonIcon
..FaceId = 137
..Enabled = True
..OnAction = "SayHello"
End With
End With

End Sub

Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars("My Bar").Delete
End Sub

Sub SayHello()
MsgBox "Hello there"
End Sub

You can add as many buttons or other menu items as you like.

HTH,
Bernie
MS Excel MVP