Excel 2010 Bible

Chapter 45: Creating Custom Excel Add-Ins

913

Installing the add-in

Now it’s time to try the add-in. Choose File ➪ Excel Options ➪ Add-Ins. Select Excel Add-ins

from the Manage drop-down list, and click Go.

Excel displays its Add-Ins dialog box. Click the Browse button and locate the change case.

xlam add-in that you just created. After you do so, the Add-Ins dialog box displays the add-in in

its list. Notice that the information that you provided in the Properties panel appears here. Click

OK to close the dialog box and open the add-in.

Before Excel 2007, it was much simpler to provide a way to execute a macro in an add-in. It was done

by using a CommandBar object, which display menus and toolbars. Excel 2010 still supports

CommandBars for compatibility. All changes made to a CommandBar object are displayed in the Menu

Commands or Custom Toolbars groups of the Add-Ins tab.

If you would like to modify change case.xlsm to use CommandBars, add these two procedures to

the ThisWorkbook code module:

Private Sub Workbook_Open()

Set NewMenuItem = Application.CommandBars(“Worksheet Menu Bar”) _

.Controls(“Tools”).Controls.Add

With NewMenuItem

.Caption = “Change Case of Text...”

.BeginGroup = True

.OnAction = “ChangeCaseofText”

End With

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next

Application.CommandBars(“Worksheet Menu Bar”).Controls(“Tools”). _

Controls(“Change Case of Text...”).Delete

End Sub

When the add-in is opened, this code adds a new control to the Menu Commands group of the Add-Ins

tab. When it’s closed, the new control is removed. Simple and easy.

Modify the User Interface the Old Way