Auto Create Custom Menu Buttons

[Shaker]

Hi,
I have developed a certain Macro,
And asign it to a Custum Menu Button,
But when try to distribute it to others I have faced a problem of that they
can't see the custom menu button. And I need to do it manually per each PC.


How may I automate this process.

Help

 

[Tom Ogilvy]

The best way to do this is to create your custom menus or menu modifications
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. If the button is for general use and not particular to a single
workbook, then you would probably want to make you workbook and addin.

Probably not an option for a single button:
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.

 

[windsurferLA]

The best way to do this is to create your custom menus or menu modifications
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. If the button is for general use and not particular to a single
workbook, then you would probably want to make you workbook and addin.

Probably not an option for a single button:
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.

The task of custom menus is a lot easier with the following:

John Walkenbach has a menu maker utility that you can utilise at
http://www.j-walk.com/ss/excel/tips/tip53.htm

It only goes to 3rd level menus, but that is usually good enough.