Part VI: Automating Excel
This book’s website at http://www.wiley.com/go/excel2019bible contains the original version of
the workbook (change case.xlsm), plus a version after it was converted to an add-in (called
(addin)change case.xlam). Neither file is locked, so you have full access to the VBA code and
UserForm.
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. It also con-
tains one VBA module and one UserForm.
Learning about Module1
The Module1 code module contains one procedure that displays the UserForm. The
ShowChangeCaseUserForm 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:
Sub ShowChangeCaseUserForm ()
If TypeName(Selection) = "Range" Then
UserForm1.Show
Else
MsgBox "Select some cells."
End If
End Sub
For information on how to navigate the Visual Basic Editor, including using the Project Explorer to find
modules, see Chapter 42, “Introducing Visual Basic for Applications.”
Learning about the UserForm
Figure 48.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.
Refer to Chapter 44 for details about how the code works.
Testing the workbook
Before you convert a workbook to an add-in, test it when a different workbook is active 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.
