Copying custom toolbars for network use

[Dan15]

Here is the situation: First I created a custom toolbar in excel on one machine. I attach the custom toolbar and transfered this file to another machine on the network. This custom toolbar opened with the file normally. I made more changes to the custom toolbar and saved the file. When I tried to put this on another machine, the original custom toolbar was displayed, not the modifed version of the custom toolbar.

The question is: How do I modify a custom toolbar and transfer it to other computers on the network?

Thank you

 

[gnmaier]

Dan,

by far the best solution, in my opinion is to create the custon toolbar or menu **in code**. Call the code to create the toolbar in the Workbook_Activate() event handler and call code to delete the toolbar in Workbook_Deactivate(). Although this is a little bit of messing around the first time you do it after that it is quite easy and a clean solution.

I highly recommend John Walkenbach's book for a description of the code (

http://www.j-walk.com).

Here is a bit of code to create a custom *Menu* rather than a toolbar, but it will give you an idea of whats involved. Good Luck.

Graham

=======================================


Put the code below in ThisWorkbook
''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''
Private Sub Workbook_Activate()

' Create custom toolbars
Call MakeToolBar


End Sub


Private Sub Workbook_Deactivate()
' Delete Marksbook Toolbar
Call DeleteToolBar
End Sub

''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''
Put the code below in a general module

''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit

'Put code like this in a general module




Const conMenuName As String = "&Marksbook"

Sub MakeToolBar()
' Creates Marksbook custom toolbar

Dim MenuCount As Integer
Dim NewMenu As CommandBarControl
Dim NewItem As CommandBarControl
Dim sheet As Worksheet

' Delete existing copy of toolbar
Call DeleteToolBar

' Add the menu
MenuCount = Application.CommandBars(1).Controls.Count

Set NewMenu = Application.CommandBars(1).Controls.Add _
(Type:=msoControlPopup, before:=MenuCount, temporary:=True)
NewMenu.Caption = conMenuName

' Add buttons to run macros
Set NewItem = NewMenu.Controls.Add(Type:=msoControlButton, temporary:=True)
With NewItem
.Caption = "Select Subject"
.OnAction = "ShowSubjects"
.FaceId = 1992
End With

Set NewItem = NewMenu.Controls.Add(Type:=msoControlButton, temporary:=True)
With NewItem
.Caption = "Edit Comments"
.OnAction = "EditCommentPopup"
.FaceId = 2056
End With

Set NewItem = NewMenu.Controls.Add(Type:=msoControlButton, temporary:=True)
With NewItem
.Caption = "Add Marksheet"
.OnAction = "AddMarksSheet"
.BeginGroup = True
.FaceId = 53
End With

Set NewItem = NewMenu.Controls.Add(Type:=msoControlButton, temporary:=True)
With NewItem
.Caption = "Edit Marksheet Master"
.OnAction = "EditMasterSheet"
.FaceId = 8
End With

Set NewItem = NewMenu.Controls.Add(Type:=msoControlButton, temporary:=True)
With NewItem
.Caption = "Delete All Marksheets"
.OnAction = "DeleteAllSheets"
.FaceId = 1786
End With

Set NewItem = NewMenu.Controls.Add(Type:=msoControlButton, temporary:=True)
With NewItem
.Caption = "Show/Hide Toolbars"
.OnAction = "ToggleStandardToolBars"
.BeginGroup = True
End With


End Sub


Sub DeleteToolBar()
' Delete the menu before closing
On Error Resume Next
Application.CommandBars(1).Controls(conMenuName).Delete

On Error GoTo 0

End Sub

 

[Dan15]

Thank you Graham, but there are over a hundred menu items. To write code to recreate them each time the spreadsheet is opened would be very time consuming.

One way around this problem with the menus is to simply make another menu and move the drop down menu headings over to it. It is not ideal, but works for now.

Thank you any way-

-Dan