Excel 2010 Bible

Part VI: Programming Excel with VBA

910

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

Testing the workbook

Before you convert this workbook to an add-in, test it when a different workbook is active to simu-

late 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.