When toolbars dont work correctly

Excel's approach to storing toolbars can cause problems. Suppose you develop an application that uses a custom toolbar, and you attach that toolbar to the application's workbook. The first time that an end user opens the workbook, the toolbar is displayed. When the user closes Excel, your toolbar is saved in the user's XLB file. If the user alters the toolbar in any way—for example, if he or she accidentally removes a button — the next time your application is opened, the correct toolbar does not appear. Rather, the user sees the altered toolbar, which now lacks an important button. In other words, a toolbar attached to a workbook is not displayed if the user already has a toolbar with the same name. In many cases, this is not what you want to happen.

Fortunately, you can write VBA code to prevent this scenario. The trick is to never allow your custom toolbar to be added to the user's toolbar collection. One way to do this is to create the toolbar on the fly every time that the workbook is opened and then delete it when your application closes. With this process, the toolbar is never stored in the user's XLB file. You might think that creating a toolbar on the fly would be a slow process. As you'll see later in this chapter, creating toolbars with VBA is amazingly fast.

Another option is to attach the toolbar to your workbook and write VBA code that deletes the toolbar when the workbook is closed (using a Workbook_BeforeClose event procedure).

0 0

Post a comment