This workbook contains one worksheet, which is empty. Although the worksheet is not used, it must be present because every workbook must have at least one sheet.

Choose Insert Module in the VB Editor to insert a VBA module (named Module1). Choose Insert UserForm to insert a UserForm (named UserForm1).

The two macro that follow are contained in the Module1 code module. The ShowUserForm procedure checks the type of selection. If a range is selected, the dialog box in UserForm1 appears. If anything other than a range is selected, a message box is displayed.

The ChangeCaseOfText procedure is a special callback procedure, with one argument, that is executed from a control on the Ribbon. See “Creating the user interface for your add-in macro,” later in this chapter. This procedure simply executes the ShowUserForm procedure.

Sub ShowUserForm()
    If TypeName(Selection) = "Range" Then
        UserForm1.Show
    Else
        MsgBox "Select some cells."
    End If
End Sub

Sub ChangeCaseOfText(ByVal control As IRibbonControl)
    Call ShowUserForm
End Sub

Figure 45.3 shows the UserForm1 form, which has five controls: three OptionButton controls and two CommandButton controls. The controls have descriptive names, and the Accelerator property is set so that the controls display an accelerator key (for keyboard users). The option button with the Upper Case caption has its Value property set to True, which makes it the default option.

Figure 45.3. The custom dialog box.

The UserForm1 object contains the event-handler procedures for the two CommandButton objects that are on the form. The following procedure is executed when the OK button is clicked. This procedure does all the work:

Private Sub OKButton_Click()
    CaseChangerDialog.Hide
    Application.ScreenUpdating = False

'   Upper case
    If OptionUpper Then
        For Each cell In Selection
        If Not cell.HasFormula Then
            cell.Value = StrConv(cell.Value, vbUpperCase)
        End If
        Next cell
    End If
'   Lower case
    If OptionLower Then
        For Each cell In Selection
        If Not cell.HasFormula Then
            cell.Value = StrConv(cell.Value, vbLowerCase)
        End If
        Next cell
    End If
'   Proper case
    If OptionProper Then
        For Each cell In Selection
        If Not cell.HasFormula Then
            cell.Value = StrConv(cell.Value, vbProperCase)
        End If
        Next cell
    End If
    Unload UserForm1
End Sub

The following procedure is executed if the user clicks the Cancel button.

Private Sub CancelButton_Click()
    Unload UserForm1
End Sub

Before you convert this workbook to an add-in, it’s a good idea to test it. You should test it when a different workbook is active in order to simulate what happens when the workbook is an add-in. Remember that an add-in is never the active workbook, and it never displays any of its worksheets.

To test it, I saved the workbook, closed it, and then reopened it. With the workbook open, I then activated a different workbook, selected some cells that contained text and then pressed Alt+F8 to display the Macros dialog box. I executed the ShowUserForm macro and tried all of the options.

Adding descriptive information is recommended but not necessary. Choose Office Prepare Properties to display the Properties panel below the Ribbon. Enter a title for the add-in in the Title field. This text appears in the Add-Ins dialog box. In the Comments field, enter a description. This information appears at the bottom of the Add-Ins dialog box when the add-in is selected. See Figure 45.4.

Figure 45.4. Use the Properties panel to enter descriptive information about your add-in.

In some situations (such as a commercial product), you may want to protect your project so that others can’t see the source code. To protect the project, follow these steps:

To save the workbook as an add-in, switch to the Excel window and activate your workbook. Then choose Office Save As. Select Microsoft Excel Add-In (*.xlam) from the Save As Type drop-down list. Enter a name for the add-in file and then click OK. By default, Excel saves the add-in in your AddIns directory, but you can choose a different directory if you like.

At this point, the add-in is created, but it’s missing one key component: A way to execute the macro that displays the UserForm. This section describes how to modify your add-in file so that it adds a new button to the Ribbon. It uses a technique known as RibbonX, which involves XML.

Specifically, we’ll add a new group that’s displayed when the Home tab is clicked. This group will contain one button, and clicking this button will execute the ChangeCaseofText macro.

The changes you need to make are done outside of Excel. Make sure that the add-in file is not open in Excel. You’ll need to use a text editor. such as Notepad.

Now it’s time to try the add-in. Choose Office 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.

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

Figure 45.7 shows the new command in the Ribbon. Click this button, and the ChangeCaseofText macro executes.

Figure 45.7. The new user interface control in the Ribbon.