Excel 2010 Bible

Chapter 45: Creating Custom Excel Add-Ins

909

Procedures in Module1

The two macros that follow are contained in the Module1 code module. The ShowUserForm pro-

cedure 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

About the UserForm

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.