Easily Deploying Customizations

When it comes to customizing Excel, your imagination is pretty much the limit. If you can imagine it, you can probably find a way to customize Excel to reflect your desires. You may even want to share your customizations with others.

In Excel, your custom macros are stored either in regular workbooks or in the personal.xls workbook, and changes to toolbars and menus are stored in a file with the .XLB extension. (In Excel, there should only be one .XLB file accessed at a time, and it is for this very purpose—managing toolbar and menu customizations.) The location of these files can vary from system to system, but you can use the Windows Search feature to locate them.

If you want, you can copy both the workbook with the macros and the .XLB file from your system to someone else's system. You just need to make sure that the other system is using the same version of Excel that you are and you need to make sure that you place the .XLB file in the same location as the existing .XLB file on the other system. The only problem with this, of course, is that when you replace the files on their system, you also get rid of any macros and/or customizations they may have previously made on their system.

To get around this problem, the best way to share macros is to add them into a file and save it as an add-in file (*.xla). The add-in should contain additional code to create the toolbar customizations and any menu items when the add-in is installed (workbook_AddInInstall event) and then remove them when the add-in is removed (workbook_AddInUnInstall event). In other words,. you are not saving the exact toolbar and menu customizations on your system, but you are using macros to recreate the customizations on the other person's system.

Creating the customizations is not too difficult, but it is still not a trivial task—and definitely beyond the scope of this tip. Menu customizations, explained properly, normally occupy an entire chapter in a good reference book.

So where should you look to find additional information? Chip Pearson has some good info on creating menus with VBA at this page:

http://www.cpearson.com/excel/menus.htm

John Walkenbach's site has a file with some example code, at this page:

http://j-walk.com/ss/excel/tips/tip90.htm

A good reference is John's Excel 2003 Power Programming With VBA, as well as the older versions of the same book (for older versions of Excel). You can find information about those books at these pages:

http://j-walk.com/ss/books/xlbook25.htm
http://j-walk.com/ss/books/bookxl19.htm
http://j-walk.com/ss/books/bookxl12.htm

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2083) applies to Microsoft Excel 97, 2000, 2002, and 2003.