Macro & Tool Bar Sharing

  • Thread starter Thread starter Juan
  • Start date Start date
J

Juan

Hi all...

looking at the reply to the post macro sharing from Rob by
Bob, I have another question regarding this...

How do I add a Tool Bar to a AddIn File so that when the
addin is loaded so is the toolbar.

regards
Juan
 
The best way to do this is to create your custom menus with a macro in the
Workbook_Open event and delete them in the Workbook_BeforeClose event. This
way you are sure you have full control over the menus and that they are
pointing to the right code when they execute.

An alternate way is to use the Attach command in the the Tools=>Customize
dialog in the Toolbars tab. This way, when your file is opened, if the
toolbar does not exist, it is created from the toolbar attached to the file.
It is still a good idea to remove the toolbar when you close the workbook by
using the Workbook_BeforeClose event.

Here is an article which talks about attaching toolbars:

http://www.microsoft.com/exceldev/articles/toolbatt.htm

Here is an article about creating commandbars with code:
http://msdn.microsoft.com/library/techart/ofcmdbar.htm

Here is another article or two you might find useful:
http://msdn.microsoft.com/library/backgrnd/html/msdn_addins97.htm
http://msdn.microsoft.com/library/officedev/odeopg/deovrcreatingexceladdin.htm
http://www.microsoft.com/exceldev/tips/addins.htm
These are about distributing applications

http://support.microsoft.com/?id=159619
XL97: Sample Macros for Customizing Menus and Submenus

http://support.microsoft.com/?id=213550
XL2000: Sample Macros for Customizing Menus and Submenus


http://support.microsoft.com/default.aspx?scid=kb;en-us;166755
File Title: Customizing Menu Bars, Menus, and Menu Items in Microsoft(R)
Excel 97
File Name: WE1183.EXE
File Size: 58041 bytes
File Date: 06/20/97
Keywords: kbfile kbappnote
Description: This Application Note can help you learn techniques for writing
Visual Basic(R) for Applications code to customize menus in Microsoft Excel
97. This Application Note contains code examples that you can use with the
following elements: menu bars, menus, menu items, submenus, and shortcut
menus.
 
Juan,

the best way is to put it in the workbook open event, building it when it is
opened. Here is an example, it goes in the ThisWorkbook code module

Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCBCtrl As CommandBarControl

'Delete CommandBar if it exists
On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0

Set oCB = Application.CommandBars.Add(Name:="myToolbar",
temporary:=True)

With oCB
Set oCBCtrl = .Controls.Add(temporary:=True)
With oCBCtrl
.Caption = "Function 1"
.FaceId = 197
.OnAction = "myFunc1"
End With
Set oCBCtrl = oCB.Controls.Add(temporary:=True)
With oCBCtrl
.Caption = "Function 2"
.FaceId = 198
.OnAction = "myFunc2"
End With
Set oCBCtrl = oCB.Controls.Add(temporary:=True)
With oCBCtrl
.Caption = "Function 3"
.FaceId = 199
.OnAction = "myFunc3"
End With
.Visible = True
.Position = msoBarTop
End With

End Sub


Also, check out John Walkenbach's FaceId utility to see what icons are
available to use at
http://j-walk.com/ss/excel/tips/tip67.htm


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top