Creating a UserForm that will change cases to Upper, Lower or Proper

This article is part of my series: VBA & Macros in Excel – A Step by Step Complete Guide and Make UserForms in VBA Excel with Total 6 Tutorials.

In this tutorial, I am going to create a userform that will change a selected cell’s text case. Three type of cases I have used here: Upper Case, Lower Case, and Proper Case. You know what Upper and Lower cases are. The Proper case is changing the first letter to Capital of every word in the text.

Creating the UserForm

This UserForm will take one information from the user: the type (Upper, Lower or Proper) the user wants to apply to the text. OptionButton controls are appropriate for this type of selection. Open a new workbook and use the following steps to create the Userform:

1. Choose Developer ➪ Code ➪ Visual Basic (or press Alt+F11) to open the VB Editor window.
2. Double-click on the workbook’s name in the Project window to activate it.
3. In the VB Editor, choose Insert ➪ UserForm. An empty new form named UserForm1 is created by the VB Editor and the Toolbox is displayed.
4. I’m going to change the name of the Userform and add a caption. You can do these using the properties window. Press F4 to open the Properties window. Properties window has so many options you can manipulate. For now, just change the following:

   Property	Change to
   Name	ChangeCaseForm
   Caption	Change Case of the Text

5. Add a CommandButton control from the Toolbox to the UserForm and then change the following properties for the CommandButton:

   Property	Change to
   Name	OKButton
   Caption	OK
   Default	TRUE

6. Add another CommandButton control in the UserForm and then change the following properties:

   Property	Change to
   Name	CancelButton
   Caption	Cancel
   Default	TRUE

7. Now, add an OptionButton control and then change the following properties. By default, this option will be active, so its Value property should be set to True.

   Property	Change to
   Name	OptionUpper
   Caption	Upper Case
   Default	TRUE

8. Add a second OptionButton control and then change the following properties:

   Property	Change to
   Name	OptionLower
   Caption	Lower Case

9. Add a third OptionButton control and then change the following properties:

   Property	Change to
   Name	OptionProper
   Caption	Proper Case

10. Adjust the size and position of the controls and the form until your UserForm looks like the UserForm shown in the figure below. The controls must not overlap.

The UserForm looks like this after adding some controls and properties.

Formatting several controls in the UserForm

Sometimes it is necessary to make several controls look alike. For example, in the created UserForm, the two CommandButtons should be the same in size and alignment. Same should be for three OptionButtons. The VB Editor provides several useful commands to help you size and align the controls. For example, we’re going to give three OptionButtons same size and alignment. Pressing CTRL select three OptionButtons and right click on the selection. You can also format choosing a command from the Format menu. See below.

You can format several controls using commands under Format menu to give them the same look.

Testing the UserForm

Well, our UserForm has all the necessary controls. Now, it’s time to display the UserForm. While you’re creating the UserForm, press F5 to display the UserForm and see whether it looks like what you want. Click the Close button (X) in the title bar to close the UserForm.

The following process explains how you can write a VBA Sub procedure to display the UserForm when Excel is active:

1. 1. Insert a VBA module by choosing Insert ➪ Module.
   2. In the empty module, enter the following code:

Sub ShowCaseChangeBox()
   ChangeCaseForm.Show
End Sub

1. 1. Open your workbook pressing Alt+F11.
   2. Choose Developer ➪ Code ➪ Macros or press Alt+F8. The Macros dialog box appears.
   3. Select ShowCaseChangeBox from the list of macros, and then click Run. The UserForm appears. See the following figure.

Displaying the Userform that we have created so far.

The OptionButton controls work, but clicking the OK or Cancel buttons do not show any effect. These two buttons need to have event handler procedures.

• Click the Close button (X) in the title bar to dismiss the UserForm.

Creating event handler procedures

An event handler is a private type Sub procedure that is executed when we perform the relevant event with the object.

Now, we shall create two event handler procedures: one will handle the Click event for the CancelButton, and the other will handle the Click event for the OKButton.

Read More: Why We Use Form Controls on a Worksheet?

Event handlers for the OptionButton controls are not necessary. They will be handled internally in the OKButton Sub procedure.

To create the Sub procedure to handle the Click event for the CancelButton, use the following steps:

1. Activate the ChangeCaseForm by double-clicking its name in the Project window.
2. Double-click the CancelButton control. The VB Editor activates the code module for the UserForm and inserts an empty procedure.
3. Insert the following statement before the End Sub statement: Unload ChangeCaseForm

The complete event handler procedure is:

Private Sub CancelButton_Click()
  Unload ChangeCaseForm
End Sub

This procedure will be executed when someone will click the Cancel button (CancelButton). The dialog box will vanish when this code executes.

The next step is to write code to handle the Click event for the OKButton control.
Follow these steps:

1. You can select OKButton from the drop-down list at the top of the module. Or You can reactivate the UserForm and double-click the OKButton control. The VB Editor automatically starts a new procedure called OKButton_Click.
2. Enter the following code. The complete event handler procedure is:

   Private Sub OKButton_Click()
   Dim WorkRange As Range
       Dim cell As Range
   
       ChangeCaseForm.Hide
   
   '   Exit if a range is not selected
       If TypeName(Selection) <> "Range" Then Exit Sub
   
       Application.ScreenUpdating = False
   
   '   Avoid processing cells out of the used area
       Set WorkRange = Intersect(Selection, ActiveSheet.UsedRange)
   
   '   Upper case
       If OptionUpper Then
           For Each cell In WorkRange
           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 WorkRange
           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 WorkRange
           If Not cell.HasFormula Then
               cell.Value = StrConv(cell.Value, vbProperCase)
           End If
           Next cell
       End If
   End Sub
   

The macro demands that the user will select a cell or a range of cells. If a range is not selected, the procedure ends. The procedure then uses three separate blocks. Only one block is executed, dependent on which OptionButton is selected. The selected OptionButton has a value of True. Finally, when user clicks OK, the UserForm is unloaded.

Testing the UserForm

To test the UserForm, follow these steps:

1. Activate Excel.
2. Enter some text into a range of cells.
3. Select the range with the text.
4. Choose Developer ➪ Code ➪ Macros or press Alt+F8. The Macros dialog box appears.
5. Select ShowCaseChangeBox macro from the list, and then click Run. The UserForm appears.
6. Make your choice (Upper Case, Lower Case, or Proper Case), and click OK.

You must try this macro with more selections, including not connected cells. If you click Cancel, the UserForm is dismissed and no changes happen.

Read More: How to add a macro to your Quick Access toolbar in Excel

Making the macro available from a worksheet button

However, I want to make an easy way as though I can execute the macro in one click. A good way to execute this macro would be to set a button form on the worksheet. You can use the following steps:

1. Choose Developer ➪ Controls ➪ Insert and click the Button control in the Form Controls group.
2. Click and drag in the worksheet to create the button. The Assign Macro dialog box appears.
3. Select the ShowCaseChangeBox macro, and then click OK.
4. The button is still selected, so you can change the text to make it more descriptive. You can also right-click the button at any time to change the text.

Now you can execute the macro to show the UserForm just clicking the button. You can also change other formats like color, the size of text and etc. See the figure below:

ShowCaseChangeBox macro has been assigned to a button on the worksheet.

Making the macro available on your Quick Access toolbar

You may want that your this macro will be available when you are working with other workbooks. You have to add a button to the Quick Access Toolbar to achieve this target. Follow these steps:

1. Make sure that the workbook containing the macro is open.
2. Right-click anywhere on the Ribbon and choose Customize Quick Access Toolbar from the shortcut menu. The Excel Options dialog box appears. The Quick Access Toolbar section is selected.
3. Choose Macros from the Choose Commands From the drop-down menu on the left. Well, our macro is listed there.
4. Select the macro’s name, and click Add to add the item to the list on the right.
5. You can also change the icon. To change the icon, click Modify and choose a new image. You can also change the Display Name.
6. Click OK to close the Excel Options dialog box. The icon will appear on your Quick Access toolbar.

Adding the Macro in the Quick Access Toolbar.

The macro is added to the Quick Access Toolbar.

The Macro is added to the Quick Access Toolbar.

Download Working File

Download the working file from the link below:

Change-case.xlsm

Happy Excelling ☕

Kawser

Hello!
Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!