Microsoft Office Tutorials and References
In Depth Information
Creating a User Interface for Your Add-In
WHErE did THosE oTHEr Add-ins coME froM?
Even before you created your first add-in, you saw there were already some add-ins
listed in the Add-Ins dialog box. Excel ships with four available add-ins; they are
not open until you select them in the Add-Ins dialog box. The four add-ins are:
The Analysis ToolPak add-in, which provides an expanded set of analysis tools
not available in standard worksheet functions and features
The Analysis ToolPak VBA add-in, which provides an expanded set of
functions for your VBA programming code
The Euro Currency Tools add-in is a tool for converting and formatting the
euro currency
The Solver add-in is a what-if analysis tool that attempts to find an optimal
value for a formula in one cell while considering constraints placed on the
values in other cells
crEATing A usEr inTErfAcE for your Add-in
Now that the add-in has been created and installed, you need to provide your users with the
ability to access the functionality. As it stands right now, all that’s happened is the add-in is available
behind the scenes. However, because the SheetManager add-in’s functionality is tied to a UserForm,
you’ll need to establish a way for users to click a link of some kind that calls the UserForm.
Before the Ribbon came along, a custom worksheet menu item was created using the CommandBar
object. For this example, I named the menu item SheetManager, and it appears on the Tools menu.
The good news is, Excel versions 2007 and 2010
still support CommandBars , and you can use the
same code to achieve a user-friendly custom menu
interface that is compatible with every version of
Excel starting with Excel 97.
For versions of Excel prior to 2007, a menu
item named Sheet Manager will appear in the
Tools menu, as shown in Figure 22-13. For
versions 2007 and 2010, the menu item named
Sheet Manager will be displayed in the Menu
Commands section of a new tab on the Ribbon
named Add-Ins. The Add-Ins tab appears when
you apply custom add-in code. In any case,
clicking the Sheet Manager menu item executes
the macro that calls the UserForm, as shown in
Figure 22-14.
figurE 22-13
Search JabSto ::

Custom Search